7 Replies Latest reply on Oct 21, 2014 4:37 AM by LyndsayHowarth

    Select a specific repeating field

    mustang70

      Hi All

      I am trying to write a script that will select a specific repeating field based on a find result. Can anyone help?

      I have a FM12 database for raising client invoices and I have the odd occaision where multiple payments are made against a single invoice. As this is rare, I have just set it up as repeating fields for "Amount" and "Date paid". For tax reporting purposes I need to list payments (not invoice totals) within a period of time. I have set up a list layout for this function but I need it to choose the correct payment from the repeating field (rather than list all of them). Is this possible?

      Thanks

      Rob

        • 1. Re: Select a specific repeating field
          BruceRobertson

          This is exactly why you should not be using repeating fields.

          • 2. Re: Select a specific repeating field
            mustang70

            Yes, thanks Bruce.... I do understand I'm trying to take a short cut. I was trying to avoid yet another related table for a scenario that would only occur in one or two transactions a year.

            If you don't mind me asking... is it repeating fields in general you don't like or just using them in my scenario?

            • 3. Re: Select a specific repeating field
              BruceRobertson

              Repeating fields have many utility uses and are a really intriguing FileMaker feature, but when trying to make them substitute for properly normalized data I'm not a fan.

              • 4. Re: Select a specific repeating field
                keywords

                Bruce's provisos aside (which I agree with) you could try using the List ( ) function to pull out all the values. However, your comment that this is for "a scenario that would only occur in one or two transactions a year" is even more reason why you should follow Bruce's advice and use another technique. With a related table you would require just one record for each payment, instead of having a repeating field with most repetitions empty on most records.

                • 5. Re: Select a specific repeating field
                  BruceRobertson

                  As far as a literal answer to your question:

                   

                  It isn't clear how you envision using your addressing method.

                   

                  Collector script? Calc field?

                  But there are several standard feature of Filemaker that let you get the contents of a designated rep:

                   

                  GetRepetition( TO::repField; whichRep )

                   

                  TO::repField[N]

                   

                  and as keywords says:

                   

                  GetValue( List( TO::Repfield); N)

                  1 of 1 people found this helpful
                  • 6. Re: Select a specific repeating field
                    beverly

                    Hello, Rob. I agree that you should have this a separate table (even for occasionally). I have two tables for accepting $$:

                     

                         Payments

                              Payment_splits.

                     

                     

                    Payments get the full amount of a check (or other form of payment), "splits" get the division of the full amount into the appropriate invoices. This is how a lot of accounting programs work. One payment can be used for multiple invoices and invoices can have multiple payments.

                     

                    It's not that difficult and if you already have your repeating field, you can export then import to divide out the values into a related table.

                     

                    Beverly

                    1 of 1 people found this helpful
                    • 7. Re: Select a specific repeating field
                      LyndsayHowarth

                      Or better still... going back to your original suggestion....

                      It is easy to export the record's repetitions and a key field and split for importing as a new table. Then of course add a relationship... and a few summaries or such... And you are away.

                      This importance task of getting accurate data this way will be a lot less tedious in the long run.

                       

                      Sent from my iPad

                      Lyndsay Howarth

                      11th Hour Group Pty Ltd