2 Replies Latest reply on Nov 11, 2013 5:25 AM by LSNOVER

    Adding supplementary fields to an  ESS table layout


      Brain Cramp!


      I'm just starting to work with a mySQL database to which I've attached via ODBC. using FMA for Windows. Works great. I've created a layout that includes, a master person table, and portals for a related address table and a purchase table, which shows whether the person has multiple addresses (they shouldn't) and their purchase history.


      Now I'm finding that I'd like to add some supplementary fields to the layout that would be specific just to this FM application and specifically to the person data . (I don't have ALTER TABLE rights to the mySQL database in any case to create the fields there). Example: I just want a flag checkbox for persons that I think have multiple person records for future merging into a single person record.


      I'm thinking that I can create a new FM table for these fields, with a 1-1 relationship on the personID, add the table to the relationship tab, and go on merry way to add the field(s) to the existing layout. If I did this, however, I'd somehow havet to get the personIDs loaded into the new table before starting, and then ensure that if I inserted a new person into the person table that their personID would also be inserted into the FM table.


      Is this a reasonable approach?


      By the way, I cobbled together this FM layout as a replacement for a unbelievably clunky set of PHP screens. Took about 20 minutes to get the basics down with the relationships and to display everything. Very cool. It also displays pretty well on my iPad when being shared from the workstation.


      --- Larry

        • 1. Re: Adding supplementary fields to an  ESS table layout

          If you're only worried about querying said checkboxes for a positive boolean (IE search for all that have flag checked), then just set up as you've described, and set the relation to allow for creation of records in the filemaker table via the relationship.


          No need to create an entire matching set of records (and continously check for new records), but rather, the action of checking a box creates the related FM record.


          Beware of the performance issues that can come from relating ESS directly into FM. The more records you get, the slower related tasks (IE finds) will perform.


          Good luck!

          • 2. Re: Adding supplementary fields to an  ESS table layout

            As Mike said,


            Your strategy will work.  ESS does take some finesse with large record sets.  The feature set is easy to use and get started with, but it can be very frustrating to make things work acceptably in practice.  The trick is controlling the records in the SQL database that are accessible from Filemaker.


            Let us know how you make out and where you get stuck.


            Send a note to Filemaker and let them know you like and are using this feature along with the issues that are causing you problems.  This feature set could be improved dramatically with a few updates, but that is most likely to happen with support from the Filemaker community.