AnsweredAssumed Answered

Cascading Adding of Database Records

Question asked by ToddGold_1 on Jul 19, 2015
Latest reply on Jul 25, 2015 by philmodjunk

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

Outcomes