Report Writer - UGH - Table Relationships?

(0) Share

Report

Posted on by Bron

4,887

Okay - I only dabble in Report Writer enough to be frustrated by the complxity of what I perceive to be simple requests.

My client wants to add verbage to the SOP Blank Sales Order Form that if the Sales Order has a 'WIRE' (process) hold - the document will print text to that effect.

The Sales Hold information is in the SOP10104 - SOP_PROCESS_HOLDS_WORK.

If I lookup Tables - I see that the SOP10100 Table (Sales Transaction Work) is already 'linked' or related to the SOP10104. I figured (incorrectly) that I will simply need to click on tables (from the report definition) of Sales Blank Order - Click on Sales Transaction Work and I will see the SOP_PROCESS_HOLDS_WORK and simply 'add it' to the report.......

Of course............in the Related Tables Screen - I only see 4 tables that are 'linked' to Sales Transaction Work for this report...........(in database Sales Transaction Work is related to at least 10 different tables - why is only 4 showing?)

How do I get the SOP_PROCESS_HOLDS_WORK to show up in the Related Tables Window so I can add it to the report?

There is a step I'm missing............UGH..............I love my clients that use Crystal Reports and really wonder why anyone uses Report Writer.......

Thanks in advance......

*This post is locked for comments

All responses (9)

Answers (0)

Victoria Yudin 22,766 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Re: Report Writer - UGH - Table Relationships?

Bron,

I definitely agree with having the trigger if you are doing this in Report Writer. Much safer for all involved.

This thread illustrates perfectly the reason why I try to use Crystal Reports whenever possible when customers need modifications to SOP reports. Something that would take 5 minutes to do in Crystal requires all this work and effort in Report Writer and a SQL trigger as well.
Bron 4,887 on at

Like (0)

Report

Re: Re: Re: Re: Re: Re: Report Writer - UGH - Table Relationships?

Okay - that does give me an idea for a workaround.

I will write a SQL trigger on the SOP10104 table. When a record gets created - write a value to the User Defined Field automatically - then I can write a calculation in report writer for the text.......

Probably better than asking users to update the UDF field independently.........I should have thought of this sooner.......I'm getting too old for this............

Thanks!!
Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: Re: Re: Re: Report Writer - UGH - Table Relationships?

I don't really see a way to handle this with the SOP10104 table. You could have them use one of the user-defined fields to indicate there is a wire as well as using a process hold. The process hold would put a hold on the order and the user-defined fields table is already linked and the field could be added to the report.
Bron 4,887 on at

Like (0)

Report

Re: Re: Re: Re: Report Writer - UGH - Table Relationships?

Thanks for the reply.

I tried removing it - which allowed me to add the SOP10104 (processing holds table). But then the report doesn't work (their are sop10200 fields on report) and I can't add back the sop10200.

So - is there a workaround that allows both the sop10200 and sop10104 to be on report?

Thanks...........
Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: Re: Report Writer - UGH - Table Relationships?

Report Writer has a restriction -- you can only have 1 branch that has a one-to-many relationship when you are linking tables for a report. Both the SOP10200 and SOP10400 are tables that have one-to-many relationships with table SOP10100. That is why you need to remove the SOP10200 relationship then add the SOP10400 relationship to SOP10100.
Bron 4,887 on at

Like (0)

Report

Re: Re: Report Writer - UGH - Table Relationships?

Richard - I always try Partner Source/Techknowledge search first.

I know how to add a table to a report, I also know how to 'establish' a table relationship that might not already be defined in the dictionary.

If I look under table relationships - I can clearly see that the sales transaction work table (sop10100) is already linked to the sop10104 table (process hold table). In fact the sop10100 table has at least 10 tables pre-defined as links.

The real question is.............why are the related tables restricted in the Related Tables Screen.

Ian (in previous post) is right.....If I remove the sales transaction amounts work (sop10200 - contains item details) from the report - I can add the sop10104 (process holds table)..........this is crazy........UGH..........
Richard Whaley 25,195 on at

Like (0)

Report

Re: Report Writer - UGH - Table Relationships?

They use it because it is embedded.....no other reason.

Look into Partner Source for under Report Writer for Adding Tables to a Report. It is simple but detailed process.

You can add the process hold table to the report and then write a conditional statement to print your text.
Bron 4,887 on at

Like (0)

Report

Re: Re: Report Writer - UGH - Table Relationships?

Thanks for the reply.

Unfortunately - the sales transaction amounts table is very essential to this report and can not be removed.

I wonder if there is a column in the SOP10100 table that indicates this record has a process hold (thus a record in the sop10104 tables)?????

If there was.......you could write a calculated formula based on the column........??

If not, and if I still need to accomplish this task - what are my options - if I can not drop the sales transaction amounts work table???

Thanks - your thoughts?
Community Member Microsoft Employee on at

Like (0)

Report

Re: Report Writer - UGH - Table Relationships?

Hi, not sure I can help much, but there is a logical reason why all of the tables associated with the SOP work table are not available for selection. Their abilityto be selected, depends on what other related tables hae already been selected. If you delete the table ' Sales Transaction Amounts Work* ' from the tables window in the report, and then click on 'Sales Transaction Work' and click New...you will now be able to select the Process Holds table. Why this is the case, I don't know, however there will be a sound relational reason why some tables related to a single tale cannot be selected together.