10 Replies Latest reply on Aug 3, 2015 12:13 PM by richardsrussell

    Finding, showing and counting related records when there are none.

    paulwatts

      I have tables "Jobs" (parent) and "Job items" (child) and tables "Quotes" (parent) and "Quote items" (child). The Jobs family are related to the Quotes family via the "Client".

       

      I want to be able to select a record in the Jobs layout and then, by script, find related records in Job items. I then want to be able to overwrite the Job items values (or create new), by script, using values from corresponding Quote items. There may already be some related Job items records (up to a max of 10) or there may be none. Problems arise when I try to count how many there are using Get(FoundCount).

       

      1. If I use Go to related records to get from Jobs layout to Job items layout and there are no related Job items, the selected Jobs record remains the current record and Get(FoundCount) returns 1.

      2. If I use Go to Job items layout followed by Find and there are no related Job items, all Jobs items in the table are shown and Get(FoundCount) returns thousands.

       

      I need some logic that will leave me in the Job items layout with a Get(FoundCount) of either zero or other number (up to 10), as appropriate.

      Any thoughts?

       

      Paul

        • 1. Re: Finding, showing and counting related records when there are none.
          mikebeargie

          Some of what you describe does not sound like native filemaker behavior.

           

          If a scripted find does not find anything, you should be left in a found set of zero records. You can use Get(LastError) = 401 in your script to check for the "no records found" error.

           

          For Go To Related Records, filemaker recommends:

          If there are no related records or no record in the active portal row, the script might produce unexpected results. Use the IsEmpty function to determine if there are no related records before using Go to Related Record.

          So before your Go To Related Records, just wrap in:

          if [ IsEmpty(JobItems::PrimaryKey) ]

             //Do Actions of having no related records

          Else

            Go To Related Records [ JobItems ]

          End If

           

          I'm not sure your relationships are setup correctly, or your find script is performing correctly, to be giving you these errant results. Also, make sure your JobItems layout is actually based on the JobItems table, what you described made it seem like it was based on the Jobs table.

          • 2. Re: Finding, showing and counting related records when there are none.
            erolst

            paulwatts wrote:

            I need some logic that will leave me in the Job items layout with a Get(FoundCount) of either zero or other number (up to 10), as appropriate.

             

            I'm not sure why you say "leave”, since you start out from Jobs, and may not even arrive in JobItems. Anyway …

             

            Whenever you use GTRR, you should trap for the case that there are no RR to GT, because whatever actions you have scripted for that related batch could have catastrophic consequences when applied to the starting table – which, as you have seen, is where the script remains if there aren't any related records.

             

            Try e.g.

             

            If [ IsEmpty ( JobItems::id ) ]

              # [ you now know that the related count is 0 ]

              Exit Script // or do what you need to do

            End If

            Go to Related Record [ JobItems ; show only related ]

            # [ you now are in JobItems, have isolated the related set of the Job record you came from, and Get ( FoundCount ) returns a result > 0 ]

             

            If you would just want to count the related records in JobItem from the context of Job, use Count ( JobItems::id ).

            • 3. Re: Finding, showing and counting related records when there are none.
              BruceHerbach

              Paul,

               

              Goto related record only works if there is a related record.  So as the others have stated, before running the command make sure there is a record to go to.  So in addition to what the others have stated,  you can do a couple of other things with out leaving the record/layout.

               

              To display the found count,  you can run a script that sets a global variable.  Count(JobItems::ID).  Run the script on record load and when you create a new record.  You may need refresh Object to make sure it is always displaying the currect/correct value.

               

              To create a new record you can use the MagicKey method.  To do this create a global field in the Jobs table.  Connect it to TO based on the Jobitems table.  Make sure "Allow creation of records in this table via this relationship" is checked.

               

              To create new records set up a script that does the following:

              Set Field [ new global field you just created ; "" ) // empties the global field

              Set Field[ NewJobItemsTO::JobsID ; Jobs::ID ] // Set the foreign key to the key of the current Jobs record.

               

              The new record has been created.  The global field will have the primary key for the new JobsItem ID.  You can set any/all fields you need to using the TO.  Commit the record and it should show in a portal on the layout. Don't forget to update your found count.

               

              If you need to edit a specific Jobitems record,  you can set it's Primary key into the global field and then have access to it.

               

              Just remember that if there is a value in the global record, and you set field on the TO for the new Jobitems record you are editing that record.  If there is a left over value in there from the last Job,  you may be editing the wrong record.  In short make sure you know which record you want to edit.

               

              HTH

              • 4. Re: Finding, showing and counting related records when there are none.
                richardsrussell

                In every table I create, I have a calculation field called "One", and the formula for calculating it is "=1". It's indexed. It therefore always exists in every record in that table. Do that in your "Job Items" table.

                 

                Then, within "Jobs", create a calculation field with the formula

                  Count ( JobItems viaJobs::One )

                with number result and NO CHECK MARK in "Do not evaluate if all referenced fields are empty". (Since the formula is based on a related field, the result can't be indexed.)

                 

                This will return "0" for any record in "Jobs" for which there are no related records in "Job Items", and if your script discovers that fact, it can just move on to the next record.

                • 5. Re: Finding, showing and counting related records when there are none.
                  erolst

                  Richard S. Russell wrote:

                  In every table I create, I have a calculation field called "One", and the formula for calculating it is "=1". It's indexed. It therefore always exists in every record in that table. Do that in your "Job Items" table.

                   

                  Then, within "Jobs", create a calculation field with the formula

                    Count ( JobItems viaJobs::One )

                   

                  Since you only need to check that in the script, why create an additional field just for that purpose?

                   

                  Besides, there already should be a field in every table that's never empty and consequently can be counted reliably; it's called “primary key” …

                  • 6. Re: Finding, showing and counting related records when there are none.
                    richardsrussell

                    The "One" field is useful for all sorts of different purposes. This just happens to be one of them. And, of course, the essential virtue of the primary key is that it's guaranteed to be different in every record, whereas "One" is guaranteed to be the same.

                    • 7. Re: Finding, showing and counting related records when there are none.
                      erolst

                      Richard S. Russell wrote:

                      And, of course, the essential virtue of the primary key is that it's guaranteed to be different in every record, whereas "One" is guaranteed to be the same.

                       

                      I fail to see what that has to do "of course" with the topic at hand. The desired virtue here is being non-empty.

                       

                      And you still don't need that calculation field …

                       

                      But let's not quibble about this. My FileMaker “education” consisted of employing using lots of portals and unstored calculation fields, the latter of which is a habit I'm trying to break, so I may be a bit emphatic about this.

                       

                      If you want a static count, create a calculation (Get(FoundCount)) or summary field in the table to be counted – which single field can then can be used in all related tables; and if you really only need that information “on demand” for script branching, then no field is necessary.

                      • 8. Re: Finding, showing and counting related records when there are none.
                        richardsrussell

                        Yes, "The desired virtue here is being non-empty." But, as I said, the "One" field is useful in lots of other contexts as well. And one of its virtues is that it's easily displayable (and thus searchable) as a checkbox field with the single value "1". It's quite versatile, which is why I always want it available.

                        • 9. Re: Finding, showing and counting related records when there are none.
                          erolst

                          Richard S. Russell wrote:

                          And one of its virtues is that it's easily displayable (and thus searchable) as a checkbox field with the single value "1"

                          Such a field may have some uses, though it seems to me like a holdover from the pre-FM7 era when it was employed to implement a Cartesian relationship. The uses I can foresee are usually implementable by using a global calculation field.

                           

                          Be that as it may, but – if that field has the same value in every record, what use would have searching in it give you? (Or displaying it, for that matter?)

                          • 10. Re: Finding, showing and counting related records when there are none.
                            richardsrussell

                            No value at all searching for it in the table in which it exists. The only value comes in searching for it from a related table, to see if there's a valid match of any kind.