Sorry, but the "alphabet soup" makes this really hard to work with. Actual table names where we can see the purpose of each table makes things a lot simpler to work with.
then sorted by a field in Table C
Does not appear to be supported by your data model given the many to many relationship between tables A & B. Any given record in A may be linked to any number of records in B and Vice Versa.
Understood about the soup sorry.
Problem: I wish to create a report using a field from tableA, listing all it's 'linked' records from tableB. TableC & D hold information for each of the records from TableB that I wish to include. It would almost fall into a hierarchy situation but I need the 'Join' table because of the many to many relationship. I need to get past the join table to include the 'one record information' from TableA in my report.
I hope that makes sense?
I think that you want to find one record in Table A and list data from tables B, C and D for all B records that link to the specified record in Table A.
If so, then this approach may work:
Perform a find for the record in table A, or perform a script from the current record in table A, to:
Put any data you need from A in global variables.
Use Go to Related Records to pull up a found set of Table D records on a layout based on Table D. Put the global variables of Table A data on this layout in the header. Sort your records by the __pk fields from Tables B and C. put fields from these two tables in a pair of sub summary layout parts with the same pk fields specified as the "when sorted by" (Break) fields.
Thanks again Phil,
I understand what you are saying and no doubt it will work.
However, I was wondering if you could give me a jump start on the script I would use to execute your theory.
I have made a 'Resource' table with no relationships to any other table having a global field.
I have attached a photo of the tables I am using and a report I can generate. Based on our previous conversations (alphabet soup), here are the actual tables.
Table A = Programs
Table B = Courses
Table C = Competency
Table D = Outcomes
I am trying to get information from the Programs Table to appear at the top of the attached report. There will only be one record from Programs Table.
The TESTReport is based on the Table 'Outcomes' and you can see how I have used the sub summary parts in the Layout TESTReport photo.
I just want to add the information I need from the Programs Table to the top of that report - Please help, and once again, thank you.
Sorry I resized the Layout TESTReport so it would fit on the page and messed up where the fields and tables line up.
- 'Outcomes' should be in the 'Body part' of the layout,
- field 'Competency' in Sub-summary by Competency::Competency (Leading),
- and all the Course information in the Sub-summary by Courses::Course Name (Leading)
Sorry if I have made it confusing.
Can you produce the report that you need except for the need include data from a record in Programs?
Yes. The attached report is generated perfect. I can not seem to be able to get the information I would like from the Programs to appear at the top of that report. One program could have several reports so I am trying to create that. In the Programs Layout, I select courses within a portal that would be included in that specific program. All that works great, no duplications of courses during that process either.
So while on the programs layout at the start of your script use Set Variable steps to capture data in global variables.
Set Variable [$$ProgramName ; value: Program::ProgramName ]
Then on your report layout, put the layout text <<$$ProgramName>> on your layout to show the value of the variable. (This is called a merge variable and you can also insert them from the Insert menu while in layout mode.
One last thing and I (or I should say we, you...) have it licked. Your 'Set Variable' steps works perfectly, thank you very much for that. I created a button 'Program Report' on the Program Layout to run the 'Program Report' script as seen in the attached.
The only thing I need to do now to make this perfect is, in the Program layout I can select the courses that would make up the program by using a portal with a Pop Up Menu, using a script 'Select Course' to select the courses that would make up that program. All that part works great.
But when I select the 'Program Report' button which runs the assigned script and I end up on my Program Report Layout, all the courses are listed in my report. I need only the ones I selected in the portal to show up.
Lets say I have 10 records in my 'Course Table.' In my Programs Layout, I select 4 of the records from that Course Table in the portal with the Popup. When I hit the button to run the Program Report script, all 10 records are listed. I only wish the 4 to be listed.
Any suggestions sir?
Here is the Program Layout with the Portal to select the courses.