2 Replies Latest reply on Feb 9, 2015 4:30 AM by MartonNagy

    How to list non-repeating value pairs of related fields?

    MartonNagy

      Title

      How to list non-repeating value pairs of related fields?

      Post

      Hi All,

      I bumped into a problem. I have a parent table with Participant Infos and a child table with Participant Appointments. The Participant Infos contain the plate number and the Appointments contain the time + date of their sessions. I would like to list for a given date all the appointment times + plate number pairs but without repeating the same pair of results.  What I managed is to set up a portal and list out all the related records to the date with the time + plate numbers, but then I dont know how to get rid of the repeating pairs. For example I have:

      9:30 TLL-566          and I want =====>   9:30 TLL-566

      9:30 TLL-566                                          10:00 FGG-111

      10:00 FGG-111                                      10:30 ATB-222

      10:30 ATB-222

       

      I tried to concatenate the time + plate number and create a value list but some indexing problems dont let me do that. Any other suggestion would be great!I am new to filemaker, never did scripting....

      Thanks!

        • 1. Re: How to list non-repeating value pairs of related fields?
          philmodjunk

          Plate number as in vehicle license plate number? That seems an odd choice for an ID.

          Why would there be two entries with the same unique combination of time and plate number? If you were to set your system up to prevent such duplication, you wouldn't have this problem in the first place.

          I tried to concatenate the time + plate number and create a value list

          Can you describe those "indexing problems"? Did you try using a text field with an auto-enter calculation to concatenate the values?

          Not only should that work as an indexed field for a value list, a validation rule specifying unique values could catch and prevent the creation of a duplicate value pair in the first place.

          • 2. Re: How to list non-repeating value pairs of related fields?
            MartonNagy

            Hi Phil,

            The ID which serves to match the records between the tables is the name of the participant. So for a given participant in the Participant table I have several participant appointment records in the Appointments table. 

            My database is set up in a way that for one participant there can be multiple appointments with the same exact times. These records with the same time values however differ based on other fields (experiment type, experimental lab).  I would like to list out for a given day all the non repeating time + license plate numbers. 

            What I tried for the concatenation: 

            1. I set up a calculation to join the time + license plate numbers for every record in the Appointments table. Like: appointments::time&"____"&participants::license_plate_number. 

            2. Then I tried to make a value list out of my calculation field results. But I got a warning that my value list wont work because the field i want to use cannot be indexed.

            3. I tried to change the setting for the calculation field in the Storage options to automatically create indexes. But I got the error that my calculation cannot be indexed because it uses a "related field, a summary field, an unstored calculation field, or a field with global storage."

            I guess there is something going on with how the relationships are set up in my tables. How can I try to figure out the problem?Where should I start?

             

            Thanks for your help!