1 2 Previous Next 19 Replies Latest reply on Nov 23, 2016 10:40 AM by erolst

    Filtering printed label (report)?

    ezeitgeist

      I am setting up an export/print labels from a couple of related tables (FileMaker Pro 15 Help ). I am wondering, is it possible to somewhere add a filtering step to filter out potential duplicate entries? Or filter by date or the like? My main table lists meetings had with various contacts. I want to allow the user to print mailing labels to those contacts met through meetings. Some contacts they have had multiple meetings with so would likely create duplicate labels, which is undesirable.

        • 1. Re: Filtering printed label (report)?
          ezeitgeist

          I think it is possible, the "Print Labels" layout gives you a list of your labels. I should be able to add a filter to that in the step before actually sending to a printer (if I script that step). How would I get it to filter to only one name? As in, if "Betty White" shows up twice with the same address (I may know 2 Betty Whites), that it only shows one of them (or conversely hides any duplicates)?

          • 2. Re: Filtering printed label (report)?
            erolst

            "Filtering" in a table means manipulating the found set.

             

            You're not being too specific, but can we assume that you want to keep only one meeting record per contact (and maybe only the most recent one at that, because you need that data on the label)?

            • 3. Re: Filtering printed label (report)?
              ezeitgeist

              Yes, 100% what you wrote. Sorry for my poor explanation. I'm still learning the correct FMP terminology. Reducing the found set. Can I possibly utilize  a  Constrain Found Set?

              • 4. Re: Filtering printed label (report)?
                erolst

                ezeitgeist wrote:

                Reducing the found set. Can I possibly utilize a Constrain Found Set?

                Not in this scenario, because how would you “phrase” the request?

                 

                There's an easier way: write a short script like …

                 

                Sort Records [ by: contact (the way you prefer the labels to be sorted, or any direction), meetingDate (descending) ]

                Go to Record [ first ]

                Freeze Window

                Loop

                  If [ $lastContactID ≠ Table::contactID ]

                    Set Variable [ $lastContactID ; Table::contactID ]

                    Go to Record [ next ; exit after last ]

                  Else

                    Omit Record [ no dialog ]

                  End If

                End Loop

                # [ print etc. ]

                 

                This will only keep the first record of each contact's meeting records in the found set, and due to the sort order, that is the most recent one.

                 

                Note that there is a more efficient way to get rid of a yet-to-be-determined number of records per sorted group, but it is more complicated to set up and code; unless we're talking thousands of records, this simple loop should be fast enough.

                • 5. Re: Filtering printed label (report)?
                  philmodjunk

                  If you want one label per contact rather than one label per meeting, use a layout based on contacts rather than meetings. You can perform a find on your contacts based labels layout to find records with criteria specified for data in a related occurrence of meetings and yet you will only get one label per contact even though more than one meeting record linked to that contact may match your specified criteria.

                  • 6. Re: Filtering printed label (report)?
                    ezeitgeist

                    erolst -- Trying your recommendation. Pardon my ignorance, what does the last line "# [ print etc. ]" refer to? Thank you!

                    • 7. Re: Filtering printed label (report)?
                      ezeitgeist

                      I tried the script. Integrated it into a button that opens the layout in a New Window. For some reason, I am still getting the duplicates. Do I possibly need to define the $lastContactID Variable BEFORE the If statement start? Could the Label layout somehow mess with it? Should I change the script from a button to an OnLayoutEnter trigger?

                       

                      Screen Shot 2016-11-21 at 2.13.00 PM.png

                      • 8. Re: Filtering printed label (report)?
                        erolst

                        ezeitgeist wrote:

                        Do I possibly need to define the $lastContactID Variable BEFORE the If statement start?

                        You could, but that's totally superfluous.

                         

                        Think about the sequence of events. The local $variable does not exist when you start the script, and it does not exist when you enter the loop. This means regardless of what value the first record holds, that value cannot possibly be identical with the contents of a not-yet-existing variable - i.e. <nothing>.

                         

                        That means that the script in the first iteration will automatically enter the True section of the If-block, and the variable will be created.

                         

                        This works in FileMaker because it allows you to refer to non-existing variables. Other platforms may be more strict, and there you may have to declare the variable before referencing it.

                         

                        Apart from that, the script hinges on the correct sort order. Check that before looking for other, more esoteric causes.

                         

                        You don't perchance have FM Pro Advanced?

                        • 9. Re: Filtering printed label (report)?
                          ezeitgeist

                          Will check sort. Yes, I have FMP Advanced 15.

                          • 10. Re: Filtering printed label (report)?
                            erolst

                            ezeitgeist wrote:

                            Yes, I have FMP Advanced 15.

                            Then you can use the Script Debugger to run through the script step by step, and watch what is happening (in slow motion, so to speak).

                            • 11. Re: Filtering printed label (report)?
                              ezeitgeist

                              Oh, I have never used that feature. Thank you, I will give it a go!

                              • 12. Re: Filtering printed label (report)?
                                ezeitgeist

                                I moved it to be OnLayoutEnter. I ran the Script Debugger. As soon as I hit "continue", I get my print screen, though the error "[101] Record is missing" shows on the Debugger. What does that mean?

                                • 13. Re: Filtering printed label (report)?
                                  ezeitgeist

                                  phil -- How would I set up a find on a table based on criteria from a related table? How it is setup is that All Users have access to the contacts table, and when a Connection is made, it creates a Connection Record (ConnectionID) and associates the UserID. A labels layout based on Contacts table would have All contacts. What could I script to reduce based on UserID since the UserID only appears in the Connections table? That does seem simpler, I just do not know how to do that.

                                  • 14. Re: Filtering printed label (report)?
                                    ezeitgeist

                                    PS To test the script, I removed the "print" aspect and moved the Set Variable to before the If statement. A weird thing happened. It now omits the FIRST record (which is NOT a duplicate), but leaves the last 2 records, which are the only duplicates.

                                    1 2 Previous Next