Displaying records that appear multiple times

[Skiffy]

Hi everyone,

I'm not quite sure how to express myself on this one, but I'll give it a try. I have a list of trusts (it could be companies too) that have people. Some people are involved in more than one trust. I want to print a list of all the people, sorted by the trust using a layout. I wrote a script to do this and it finds all the records, but only prints the information about a person once. So the next trust that the person appears in omits their name. How can I get the information to appear again under the second or third trust? I attach the database and the script is "See All ID Held" and the layout is "Report of All ID Held by Client". The button on the Home Screen is "Print a Report of All ID Held".

 

Many thanks,

 

Skiffy

[Ocean West]

Skiffy,

 

I temporarily removed the file to protect you as the file may have personally identifying information 

[Ocean West]

I will review the file and return to you via back channel.

[Ocean West]

This is a case where you need to use a Virtual List Table to derive the needed number of rows so that the records can be sub-summarized and data that naturally only exists once can be "duplicated" so that it appears in another sub summary section.

 

on your join table i added the following calculation

Let([
theList =List ( Identification::Identification ID )
  ;
prefix = Join ID ;
suffix = Client Matter Number ;
break = "|" 
];
prefix & break & 
Substitute (
  theList ;
  "¶" ; break & suffix & "¶" & prefix & break
)
& break & suffix
)

This results in the following for each record in the join table

156|00132|45012-1001
156|00135|45012-1001
156|00136|45012-1001

When that field is the array in the virtual list table looks like this:

 

 

 

And the relationship looks like this :

 

 

then using your layout that was targeting the identification table I changed it to the REPORT table

changing fields referenced thru the new relationship.

 

adjusting the script to go to the right layout and only find the found set that contains records.

 

here is the fields definitions of the Virtual Table

 

Currently this report will ALWAYS show ALL clients and all people with all identifications, this could be scripted and modified

so that the array is not hard coded but can be set via a script making the output dynamic based on users found set.

 

here is a small snippet of the report, cropped a bit as I didn't know if this is truly "sample" data or real data.

 

 

 

Sending you the file on the back channel.

[comment]

on your join table

 

If there is a join table, then why is this such a big problem?

[Ocean West]

if it was a matter of seeing  just the Persons summarized by Clients on the report that would be easy from the join table

 

but in this case the report was to see all the "identifications" for a person summarized by the client

 

Client --<  join  >--- person ---< identifications