Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Report Writer - UGH - ...
Microsoft Dynamics GP (Archived)

Report Writer - UGH - Table Relationships?

(0) ShareShare
ReportReport
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

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    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 Profile Picture
    Bron 4,887 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Bron 4,887 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Bron 4,887 on at
    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 Profile Picture
    Richard Whaley 25,195 on at
    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 Profile Picture
    Bron 4,887 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans