2 Replies Latest reply on Jul 25, 2015 7:43 PM by philmodjunk

    Cascading Adding of Database Records

    ToddGold_1

      Title

      Cascading Adding of Database Records

      Post

      I am struggling to solve a seemingly simple design issue in my FileMaker solution.  I have a complex enterprise solution for commercial real estate that I am creating with multiple core tables and numerous many-to-many relationships.  When adding new records it is essential to be able to link other records to the record being added. 

      For example (and this is just one of many): when adding a “Contact”, the database structure requires “linking” a “Company” via a many-to-many relationship.  In my database it is essential that a “Contact” has the ability to be “linked/joined” to multiple companies.  Not too challenging. 

      When clicking the “Add Contact “ button my database makes sure a duplicate record does not exist by launching a “Contact Search” layout.  Many times it is not simple to determine if a “Contact” already exists.  Sometimes a nickname is used by many contacts, or the spelling used in the database is not what was suspected.  So, performing a “Contact Search” is essential to ensure data quality.

      A variable is set when the “Contact Search” layout is launched to allow the user to click on the “GoTo” button if the Contact exists.  On the “Contact Search” layout I coded “Preview”, “GoTo” and “Link” buttons on each row as the layout is created on a “List View”.  An incremental search field in the header allows multiple fields, such as first name, last name, nickname, etc., to be searched at once.  As characters are typed in the search filed, the list becomes smaller and smaller. 

      The “Preview” button is designed to always be available so that the user can quickly determine if the identified record is the Contact that the user was intending to create – avoiding a duplicate.  If the search returns no match, a dialog box (using the Layout Dialog) automatically launches inviting the user to add a Contact since no record was found to exist.

      When creating the Contact record it is essential to link the Contact to a Company.  So, just like the duplicate contact search, when the “Link” Company button is clicked on the Contact add layout, the Company Search layout is launched.  But, it gets complicated when the “Company” record is not in the database when adding the Contact record.   I call this process “Cascading Adds” because each dialog layout window must remain locked and available to step back through as each layer of additional linked records are opened and/or closed.

      Using variables, I have been able to do this.  When adding a Company I must also link to an “Address” record because in my solution multiple Contacts and Companies can be related to multiple addresses.  Think Starbucks – multiple locations for the company.  I must also link to other core tables.

      The ability to ”lock” a layout as a “dialog-style” in FileMaker 13 was a huge help in reducing this complexity.  However, as the cascading addition of records gets many layers deep, it is difficult to manage the variables that need to be either maintained or cleared.  I fear my solution to this issue is more complex than necessary.

      This one issue has been a major hurdle in completing my design...I started with FileMaker 7!!

      I am looking for someone to share a simple solution to this issue or if this truly must remain as complex as it seems to me...I would welcome the opportunity to hire a developer to resolve this one key element which is essential to launching my application.  

      I am struggling to solve a seemingly simple design issue in my FileMaker solution.  I have a complex enterprise solution for commercial real estate that I am creating with multiple core tables and numerous many-to-many relationships.  When adding new records it is essential to be able to link other records to the record being added. 

      For example (and this is just one of many): when adding a “Contact”, the database structure requires “linking” a “Company” via a many-to-many relationship.  In my database it is essential that a “Contact” has the ability to be “linked/joined” to multiple companies.  Not too challenging. 

      When clicking the “Add Contact “ button my database makes sure a duplicate record does not exist by launching a “Contact Search” layout.  Many times it is not simple to determine if a “Contact” already exists.  Sometimes a nickname is used by many contacts, or the spelling used in the database is not what was suspected.  So, performing a “Contact Search” is essential to ensure data quality.

      A variable is set when the “Contact Search” layout is launched to allow the user to click on the “GoTo” button if the Contact exists.  On the “Contact Search” layout I coded “Preview”, “GoTo” and “Link” buttons on each row as the layout is created on a “List View”.  An incremental search field in the header allows multiple fields, such as first name, last name, nickname, etc., to be searched at once.  As characters are typed in the search filed, the list becomes smaller and smaller. 

      The “Preview” button is designed to always be available so that the user can quickly determine if the identified record is the Contact that the user was intending to create – avoiding a duplicate.  If the search returns no match, a dialog box (using the Layout Dialog) automatically launches inviting the user to add a Contact since no record was found to exist.

      When creating the Contact record it is essential to link the Contact to a Company.  So, just like the duplicate contact search, when the “Link” Company button is clicked on the Contact add layout, the Company Search layout is launched.  But, it gets complicated when the “Company” record is not in the database when adding the Contact record.   I call this process “Cascading Adds” because each dialog layout window must remain locked and available to step back through as each layer of additional linked records are opened and/or closed.

      Using variables, I have been able to do this.  When adding a Company I must also link to an “Address” record because in my solution multiple Contacts and Companies can be related to multiple addresses.  Think Starbucks – multiple locations for the company.  I must also link to other core tables.

      The ability to ”lock” a layout as a “dialog-style” in FileMaker 13 was a huge help in reducing this complexity.  However, as the cascading addition of records gets many layers deep, it is difficult to manage the variables that need to be either maintained or cleared.  I fear my solution to this issue is more complex than necessary.

      This one issue has been a major hurdle in completing my design...I started with FileMaker 7!!

      I am looking for someone to share a simple solution to this issue or if this truly must remain as complex as it seems to me...I would welcome the opportunity to hire a developer to resolve this one key element which is essential to launching my application.  

      Screen_Shot_2015-07-19_at_9.59.56_AM.png

        • 1. Re: Cascading Adding of Database Records
          SteveMartino

          That's a lot to digest, but I think the problem may be simple, although I'm not experienced enough to explain it, but I will try, then look for a link that describes it better.

          There's something about creating records whether thru a portal or not, when you have "Allow creation of records..." box checked in the Edit Relationship box, where if there is no matching record it creates one.  Then you create 'another one'.  I'm sure someone can explain it better. but this is what may be happening.

          Let me see if I can find a link.

          Edit: Upon checking, it's called by some the Push Technique, or maybe the Magic Key:

          http://filemakerhacks.com/2011/07/31/magic-key-and-check-box-reporting/

          This is the more important part:

          If you only need to create or update a single record, you can use the Set Field command to insert some data into the child table, e.g., Set Field [tasks::category ; “Special Events”]. If the related record does not exist, it will be created and id_volunteer will automatically be populated with the appropriate value to make the relationship valid:

          • 2. Re: Cascading Adding of Database Records
            philmodjunk

            A useful application of this method can be set up on this relationship:

            LayoutTable::someField>----RelatedTable::PrimaryKey

            use Set field to clear SomeField, then set any field in Related Table to a value and you create a new record in RelatedTable. This action copies the new PrimaryKey value (Serial number or UUID) into SomeField. To create another new record, just use set field to clear the SomeField of the newly entered matching value and you are ready to create another related record.