11 Replies Latest reply on Dec 10, 2008 9:04 AM by Orlando

    Filter Unique Number List



      Filter Unique Number List


      I am trying to create a filtered list with unique numbers only.  I have a table with order items that have an invoice # assigned to it.  There could be hunderds of items on a single invoice.  I want to list the invoice #s in the client table but only want to show each invoice # once.  The only list I have been able to create so far shows each invoice # multiple times.


      Any suggestions?

        • 1. Re: Filter Unique Number List



          Thank you for your post.


          I don't know how you have your tables set up.  It sounds like you have the Invoice number assigned to each item in the invoice.  You may want to consider the following:


          Client table

          Client ID

          <other client info>


          Part table

          Part ID

          <other Part information>


          Invoice table

          Client ID

          Part ID

          Invoice Number

          <other info like quantity, extended price, total invoice, etc.>


          You would have your Client table connected to your Invoice table through Client ID, and you would have your Part table connected to your Invoice table through Part ID.


          In your Client table, you can create a portal into Invoice table and just display the Invoice Number.  This should grab each individual Invoice Number for that Client ID and display in the portal.


          To create a portal, pull down the View menu and select "Layout Mode".


          On the left side of the screen, click on the icon just below the oval tool.  This is the portal tool.  You click on the layout and draw a box.  Let go, and it will ask you for the related table (select the Invoice table), and then the fields (select the Invoice Number).  Return to Browse mode, and you will see all the Invoice Numbers for each Client. 


          If you run into any difficulty, or need clarification, please let me know.



          FileMaker, Inc.

          • 2. Re: Filter Unique Number List

            Thanks for your quick response.


            When I do "normal" invoicing systems, I can do that, but this one is very unique and complex.  The invoicing is done through QuickBooks and QuickBooks assigns the invoice # to each line item as it transfers to QuickBooks.  I cannot use a traditional invoice table because of this. 


            I do run across this problem more than in this application also.  I would love to be able to create alist from a data set that shows me only the unique #s or text.  I have used other DBs that allow for list filters for unique values.


            Any other ideas on how to accomplish this?

            • 3. Re: Filter Unique Number List



              Thank you for the clarification.


              It sounds like you can still create a separate Invoice table that keeps track of the Invoice ID.  Once you import all the data from QuickBooks, I would then import the Invoice Number and Client ID into a separate table.  Then, remove the duplicates via a script so that there is one instance of the Invoice # in that table.  You can then still use the example I used previously.



              FileMaker, Inc. 

              • 4. Re: Filter Unique Number List

                Hi davenfla / TSGal 


                If you really need to filter a list of non-unique values to be unique there are two ways to go about it. The first is a recursive Custom Function, and the second is using a Value LIst.


                First off a Custom Function that uses recursion to filter you list. Here is a custom function I have used in the past to do something similar to you.


                  xCF_UniqueListFilter ( listToFilter ) =


                    Let( ActiveValue = GetValue ( listToFilter ; 1 ) 

                    ; // Start of calculation

                    If (

                    IsEmpty ( listToFilter ) ; "" ;

                    If ( ValueCount ( FilterValues ( listToFilter ; ActiveValue ) ) > 1 ;  "" ; ActiveValue & "¶" )  & 

                    xCF_UniqueListFilter ( RightValues ( listToFilter ; ValueCount ( listToFilter ) - 1 ) ) 

                    )  )  // End of Let function 


                If you have never used custom functions before, you can setup the custom function by going:


                • 'File > Manage > Custom Functions...'
                • Click 'New'
                • Then copy and paste the above calculation into the appropriate fields
                • Function Name: = xCF_UniqueListFilter
                • Function Parameter: = listToFilter, click the green plus sign
                • Then the rest in the calculation box.
                • Click 'OK'
                And the function will display near the bottom of the Calculation Function list, or you can view "Custom Functions" 


                • Create a new calculation field in your CLIENT tabel


                            InvoiceIDs ( Calculation ; text ) =

                              xCF_UniqueListFilter ( List ( ORDER ITEMS::InvoceNumber ) 


                And you will see the list of unique Invoice ID's you can sort the list by sorting the relationship between the TO's on the relationship graph.




                The second option, uses a Value List to filter the items for you, hand if you want to have them sorted also.


                • Setup a new Value List, 'File > Manage > Value Lists...'
                • Name it "InvoiceIDList"
                • Check 'Use values from field:' and click Specify field...'
                • Specify the ORDER ITEMS table and select you Invoce Number Field.
                • Now make sure to also select 'Include only related values starting from:' to be you main CLIENT table.
                • Now OK that and the Value List is setup.
                • Next you need to create a Calculation in you CLIENT table:

                            InvoiceIDs ( Calculation ; text ) =

                              ValueListItems ( Get ( FileName ) ; "InvoiceIDList" )


                And this should then display a unique list of invoice numbers. I have used this method a few times but not for this scenario so would be interested to hear if it works for you.


                I hope these options help and do ask if you need any help with any of these. 

                • 5. Re: Filter Unique Number List

                  Orlando - Awesome!  That's exactly what I was looking for!  I'll give it a try and update the forum.


                  Thanks again!

                  • 6. Re: Filter Unique Number List
                       Worked awesome!  Thanks again
                    • 7. Re: Filter Unique Number List

                      Glad I could help.


                      Which option did you go for, Custom Function or Value List?

                      • 8. Re: Filter Unique Number List

                        The custom function did exactly what I needed.  I have been working with FM since 91 but always stayed away from using custom functions.  Now that I see how powerful it is, I'm going to use it more.  Do you know of any good resources to learn more?


                        Thanks again.

                        • 9. Re: Filter Unique Number List

                          Good to hear.


                          I was the same when Custom Functions were introduced, but once you get started they become very very useful. Recursion took me a very long time to get my had around.


                          The number one resource is Brian Dunning's brilliant site




                          Also an demo file and artical that realy helped with recursion was by Excelisys


                          http://www.excelisys.com/web/downloads/index.php at the bottom of the page.


                          Finally there is a great tool for storing and testing your Custom Functions by Matt Haughton of First Contact Solutions called CFExplorer, a must have developer tool.




                          Good luck with your venture into the world of Custom Function and post if you need any help.

                          • 10. Re: Filter Unique Number List

                            Hi Orlando

                            "The number one resource is Brian Dunning's brilliant site"

                            as uLearnIT also knows, there will be another site for custom functions...


                            Now is on Alpha testing, but I'll come back to say when it will start !

                            • 11. Re: Filter Unique Number List

                              Hi Daniele,


                              Don't leave us hanging like that, give us some more info on this new site. When can others test it?