7 Replies Latest reply on Jan 2, 2013 8:02 PM by philmodjunk

    Solution for Auto Populating



      Solution for Auto Populating



      Use Case:


           Create a layout for Adding Vendor data so that a user can evaluate 6 Components (Tab Control) and their associated functions (portal control) for each component. Each vendor will always be evaluated based on all Components and their associated functions. 


      Vendor Table:






      Component Table





      Function Table

           For each component there are associated function stored in a seperate function table

           sf_id (Auto Num)

           sc_id (Is the ID from the component table associating the various components)



           There is a forth table for tracking all of this data for each vendor that must be auto populated based on the total number of component and associated function records (found in function table) once a new vendor record has been created.


      Vendor_Evaluation Table

           ven_id (from Vendor table)

           sc_id (taken from Function table)

           sf_id (taken from the Function table)

           function_name (also taken from the Function table)







           How can I auto populate the Vendor Evaluation Table with records from other tables once a new record has been created in the Vendor Table. For example the following example shows same vendor with multiple entries for each possible Component/Function 



           Record One Vendor_Evaluation table

           ven_id 1 = 1

           sc_id = 1

           sf_id = 1

           function_name = "EMR Integration"


           Record Two Vendor_Evaluation table

           ven_id 1 = 1

           sc_id = 1

           sf_id = 2

           function_name = "PMP Integration"


           Record Three Vendor_Evaluation table

           ven_id 1 = 1

           sc_id = 1

           sf_id = 3

           function_name = "Other Integration"


           Record Four Vendor_Evaluation table

           ven_id 1 = 1

           sc_id = 2

           sf_id = 1

           function_name = "ERX Refils"





        • 1. Re: Solution for Auto Populating

               You have referred to a Vendor_Evaluation Table, but do not show a table occurrence for it in your screen shot of relationships. You'll need that to link to records in these other tables either directly or indirectly via another related table.

               There is no link between componets and vendors either in your relationships as shown.

               You mention a function table, but your screen shot shows occurrences for two different tables that might refer to such a funciton table:

               x_function_component and x_vender_component_function

               Thus it doesn't seem to match the text that you posted describing your tables.

          • 2. Re: Solution for Auto Populating

                 You are correct I did not label properly "Vendor_Evaluation" table is actually x_vender_component_function. So let me try to articulate in detail the objective.

            Step One) User Inserts record into Vendor Table via Vendor Layout

            Step Two) Upon generating a new Vendor record in step one, behind the scenes ALL Records from x_function_component need to be inserted directly into x_vender_component_function. This includes sc_id, sf_id, function_name and also the newly added ven_id from step one is repeted for every entry inserted as indicated in the example below.

            i.e. x_vender_component_function table  should look like this once a new record is created in the Vendor table.
                 (only the first 4 columns in this table will be populated, the others will get populated upon performing an evaluation for each vendor)

                                Ven_id                     sc_id                     sf_id                     function_name
                                1                     1                     1                     EMR - Integration
                                1                     1                     2                     PILOT - Integration
                                1                     1                     3                     PMS - Integration
                                1                     2                     1                     ePrescribing
                                1                     2                     2                     Referral Management
                                1                     2                     3                     Alerts
                                1                     3                     1                     XML Standard
                                1                     3                     2                     HL7 Standard
                                1                     3                     3                     CSV Standards

            NOTE: x_function_component table technically is ONLY a reference table to be used for populating into x_vender_component_function. Im not concerned about relationships between the two vendor tables (above erd left) and the two compoenent/function tables (above erd right) hence no relationships are needed.


            • 3. Re: Solution for Auto Populating

                   I think that you need these relationships:


                   Vendor::VEN_ID = x_vendor_component_function::ven_id
                   x_Function_Component::SF_ID = x_vendor_component_function::sf_id

                   Note that the All Caps fields on the left hand side should be primary key fields, usually defined in FileMaker as auto-entered serial numbers.

                   If this notation is unfamiliar: Common Forum Relationship and Field Notations Explained

                   With this setup. a portal to x_vendor_component_function on a layout based on Vendor will list all component function records linked to that vendor. To "preload" the portal with a set of records from x_function_Component can be easily done via a script.

                   #Starting from a layout based on Vendor
                   Freeze Window
                   Set Variable [$VendorID ; value: Vendor::VEN_ID]
                   Go to Layout [x_vendor_component_function]
                   Show All Records
                   Go to Layout [x_Function_Component]
                   Import Records [ //specify importing records from x_vendor_component_function into x_Function_Component]
                   Replace Field Contents [no dialog ; x_Function_Component::ven_id ; $VendorID]
                   Go To layout [origional layout]

              • 4. Re: Solution for Auto Populating

                     Ok I have created the one additional relationship between x_Function_Component::SF_ID = x_vendor_component_function::sf_id The other one already existed.

                     As I read you example for setting up the scripts It appears to be backwards in terms of the source table and destination table. All records from the x_Function_Component shuould be inserted into x_vendor_component_function

                You code reads: Import Records [ //specify importing records from x_vendor_component_function into x_Function_Component

                     This is not what we need t do. I assume Ill switch when I get to the Import Records control. Any Specific porpetries need to be set for the new relationship?


                     Also I'm noticing that there appears to be no source table to select from when doing the import process, and lastly should I be mapping the vend_id since your next line of script indicates th ven_id variable


                • 5. Re: Solution for Auto Populating

                       Good catch on that error on my part, the import should import form x_funciton_component into x_vender_component_function.

                  • 6. Re: Solution for Auto Populating

                         Take a look at my post above I changed it indicating the confusion on why I do not see a source table for mapping to a destination table in order to impot data.

                    • 7. Re: Solution for Auto Populating

                           WIth Import Records selected, select "Specify Data Source | File" to select the file from which to import records. (You'll select the very file that you already have open here.)