5 Replies Latest reply on Nov 18, 2008 9:59 AM by ralvy

    Creating subrecords when none exist



      Creating subrecords when none exist


      Clearly a FMP newbie, but not new to databases...




      • Accounts
      • Cases


      Accounts --> Cases is a one-to-many relationship.


      The Accounts Layout has a portal showing Cases with a descending sort by Case Number. If the current Accounts record has some subrecords (Cases), the script I wrote easily creates a new subrecord with a Case Number that is conditionally incremented (each Account has Cases that start with Case Number 1). This script is executed when browsing the current Accounts record and works fine.


      I don't want the user to be able to create a new Case by just editing that Accounts Layout portal. I want a button to do this. That's why I wrote the above script. I have all fields in the portal noneditable. The Case Number on each portal line is a button that takes the user to the related subrecord where they can edit fields other than the Account Number or Case Number.


      My problem is that I can't figure out how to allow the script to create a subrecord when no subrecords already exist. When they exist, I just use the GoToRelatedRecord step and go from there. But that script step doesn't seem to help me here. I don't see a way to let the script move into the Cases Table/Layout unless there's already a related record there.


        • 1. Re: Creating subrecords when none exist

          Later ...


          I found a way to do this, but it seems kludgy. If rewrote the script so that if the GoToRelatedRecord step dropped it into the same Layout it was originally in (Get(LayoutName)="Accounts"), it does a GoToLayout (Cases) step and creates a new record there, with the proper Account and Case Numbers, else it just does things the way it used to. The first horn of the If/Then script handles cases there the current Accounts record has no Cases subrecord, and the second horn of the If/Then script handles cases where there are subrecords.


          This seems inelegant to me. Is there a more straightforward way to do this?

          • 2. Re: Creating subrecords when none exist

            Let me get this straight. You are viewing a given record in your parent table, and want to create a related record in a child table using a script instead of a portal. 


            If this is the case, you can leave the create relationships option in the relationship graph un-check-marked in the relation for these two tables (you would need this check-marked when using the portal to create the related records).  


            When in the layout of the parent table, you'll need to capture the primary (candidate) key of the current record with a Set Variable step, and when you name the variable use a "$" as the first character. Then you change context with a Go To Layout step where you go to the layout based on the child table, and then you'll need a New Record / Request step and next you'll need a Set Field step where the field in question will be for the foreign key field meant to hold the brand or copy of the parent table's primary key. The calculated result for this step will be simply the name of the variable, which you'll have to manually type from memory. At this point you'll be ready to add info for the other fields. 


            This works assuming you have layouts based on both parent table and child table. In anchor-buoy you would have one cluster of table occurrences for each table, and base the layout on the table occurrences that are listed to the farthest left of each cluster.


            Using a Sessions approach would be different because the context never changes from the one table used throughout the user's session. Not sure how they do that though, since then you can't use a Go To Layout step that changes the layout.


            • 3. Re: Creating subrecords when none exist



              Thank you for your post.


              Another possibility is in your script to evaluate the number of records in your portal.  Assuming there will always be a value in "Case Number", you can put into your script:


              If [Count (Cases::Case Number) = 0]

                 Perform Script ["Add related record" ]

              End If



              That is, if there are no related records, then you add the record.  Does that make sense?



              FileMaker, Inc. 

              • 4. Re: Creating subrecords when none exist
                   The Count() function looks like what I need here. Thanks.
                • 5. Re: Creating subrecords when none exist

                  Actually, I find this to be the simplest way:


                  Simply have the script check the value of Case:CaseNumber while in the Accounts Layout. If doesn't see a Case there, it returns 0, otherwise it returns a number greater than 0.