7 Replies Latest reply on Jul 21, 2012 7:58 AM by JimMac

    Number of records in set?

    pauhana658

      Title

      Number of records in set?

      Post

      Newbie here -- well, plenty of programming experience, but new to Filemaker and having lots of problems with FM's scripting language. 

      Here's what I want to do in plain English. I have an Invoice table with two fields for Client and Contact. The Client field gets its data from the related Client table, and the Contact field gets its data from the Contact table that is related to the Client table. When I create a new invoice, I select a name from the Clients popup. This action fills the Contacts popup with only those contacts related to the selected client. I then select a contact from the Contacts popup. All well and good and working as expected.

      But if the client I select has only one Contact associated with it, I'd like the contact field on the Invoice layout to be automatically filled with the contact name from that single Contact record. Saves a bit of effort. 

      How can I determine the number of records in a current set of related records? Get (Found Count)? If so, then where do I find and how do I use the returned value? The following doesn't work, probably because I am not specifying the related table for the found count. (Should be the Contacts table.)

      If [Get(found count)=1]

            Invoices::Contact = Contacts::Contact Name

      end if

      Any help would be greatly appreciated!

      Screen_Shot_2012-07-18_at_17.45.24.png

        • 1. Re: Number of records in set?
          philmodjunk

          Get ( foundcount ) counts the records in your current layout's found set of records, not a set of related records in another table.

          Count ( contacts::SomeFieldThatIsNeverEmpty )

          will count the related records in contacts. A "count of" summary field defined in contacts could also be used to return the number of contacts.

          On trick you might consider is to use auto-enter field options on Contact to enter data from the contacts table. This will enter data from the first related record in contacts. If there is only one related record or the first related record is the desired contact, there will be no need to select a contact--and no scripting was needed to do so.

          • 2. Re: Number of records in set?
            JimMac

            Try using both of Phil's ideas and bring over to the invoice layout a calculated Label from the contacts file, as Contact(3) or the like, where the Auto-enter would default to the field. The label would be calculated in the Contacts Table"

            ContactLabel =  "Contact("  &  GetAsText(  Count ( contacts::SomeFieldThatIsNeverEmpty )  )  &  ")"

            The Label on Invoice page would the indicate the count.

            Count(0)

            Count(1)

            Count(2).... etc.

            Jim...

             

            • 3. Re: Number of records in set?
              JimMac

              Oppps more coffee needed...

              The Label on Invoice page would the indicate the count.

              Contact(0)

              Contact(1)

              Contact(2).... etc.

              Jim...

              • 4. Re: Number of records in set?
                pauhana658

                Thanks for the replies, guys! Started with the auto-enter trick. Works just fine. Also adds a name when the related set includes multiple records, however, so I'd like to try the other suggestions, too. Would prefer that no name be entered when the related set contains multiple records.

                Again, thanks!

                • 5. Re: Number of records in set?
                  philmodjunk

                  An auto enter calculation can be set up that counts the relate records and only auto-enters a name if the count is 1.

                  If ( Count ( Contacts::ID ) = 1 ; Contacts::ID )

                  • 6. Re: Number of records in set?
                    pauhana658

                    An auto enter calculation can be set up that counts the relate records and only auto-enters a name if the count is 1.

                    If ( Count ( Contacts::ID ) = 1 ; Contacts::ID 

                    I tried entering that equation (changing ID to Contact) as a "Calculated value" in the Auto-enter section for the Invoices::Contact field, but it didn't work. No name was entered/changed in the Invoices::Contact field. Did I misinterpret where the equation should go or how it works?

                    Thanks!

                    • 7. Re: Number of records in set?
                      JimMac

                      Try..

                      If ( Count ( Contacts::ID ) = 1 ; Contacts::NameofContactField )

                      also note

                      By default, if test refers to a field that doesn’t yet contain a value, If returns an empty result. To override this functionality, deselect the Do not evaluate if all referenced fields are empty checkbox.

                      Jim...