4 Replies Latest reply on Apr 3, 2014 8:54 AM by philmodjunk

    Problems with multi-file database solution



      Problems with multi-file database solution


           Hello everyone,

           I am currently trying to design a solution for keeping track of the ordering history of various articles.

           I have a file for the orders, and one for each article type: antibodies (contains three main tables for each of three kinds of antibodies), chemicals, lab wares. I want people to be able to go through any of the databases and then click a button to order any article.

           What I have done so far is to set up the following scripts, which are supposed to

      1.           check for a matching record in the "orders" database
      3.           create a matching record if none is found
      5.           put the information about the order in a related table (through a portal)

           The table that holds the information about the articles that can be ordered is called "Orders_data".

           The table used for storing the single orders is called "OrdersDB_placedOrders".

           I am using an intermittent table for moving the data between the "sender" (e.g. the antibodies database) and the "recipient" (the orders database), which is called "Order_FieldSetters_Global".

           The scripts are meant to write data into this "Global" table; from there, the data is being sent to the necessary fields.


      Script: Antibodies_syncRecords
           -> used to enter data into "Global" table, check for matching records, and call various scripts to create a new record in the "Orders" database if needed
                Set Variable [ $Data_UniqueID; Value:Antibodies_primary::UniqueID_OrderMatching ]
                Set Field [ Order_FieldSetters_Global::g_MatchField; $Data_UniqueID ]
                Open File [ “Orders” ]
                Perform Script [ “Orders_CheckMatch” from file: “Orders” ]
                Set Variable [ $$RecordCheck_ScriptResult; Value:Get ( ScriptResult ) ]
                Set Variable [ $Data_Category; Value:"Primary Antibody" ]
                Set Variable [ $Data_Supplier; Value:Antibodies_primary::Antibody_Supplier ]
                Set Variable [ $Data_Retailer; Value:Antibodies_primary::Antibody_Retailer ]
                Set Variable [ $Data_Storage; Value:Antibodies_primary::Storage_Location ]
                Set Variable [ $Data_orderedBy; Value:Antibodies_primary::Antibody_OrderedBy ]
                Set Variable [ $Data_Articlenumber; Value:Antibodies_primary::Antibody_ArticleNumber ]
                Set Variable [ $$MatchValue; Value:Antibodies_primary::UniqueID_OrderMatching ]
                If [ $$RecordCheck_ScriptResult = 0 ]
                If [ not (FilterValues ( DatabaseNames ; "Orders" ) ) ]
                Open File [ “Orders” ]
                Select Window [ Name: "Orders" ]
                Set Field [ Order_FieldSetters_Global::g_Supplier; $Data_Supplier ]
                Set Field [ Order_FieldSetters_Global::g_Retailer; $Data_Retailer ]
                Set Field [ Order_FieldSetters_Global::g_OrderedBy; $Data_orderedBy ]
                Set Field [ Order_FieldSetters_Global::g_Articlenumber; $Data_Articlenumber ]
                Set Field [ Order_FieldSetters_Global::g_MatchField; $Data_UniqueID ]
                Set Field [ Order_FieldSetters_Global::g_Category; $Data_Category ]
                # Next step: creation of a new Record in the Table "Orders_data" and included ordering of the article
                Perform Script [ “Orders_NewRecord+PlaceOrder” from file: “Orders”; Parameter: $$MatchValue ]
      Script: Orders_CheckMatch
           -> used to check whether a matching record already exists
           Set Variable [ $$Find_UniqueID; Value:Order_FieldSetters_Global::g_MatchField ]
           Set Error Capture [ On ]
           Perform Find [ Specified Find Requests: Find Records; Criteria: Orders_data::MatchField_Manual: “== $Find_UniqueID” ]   [ Restore ]
           Exit Script [ Result: Get ( FoundCount ) ]
      Script: “Orders_NewRecord+PlaceOrder”
           -> used to create the new record inside the "Orders" database
                Set Variable [ $$Param_UniqueID; Value:Get(ScriptParameter) ]
                Go to Layout [ “OrdersDB_RecordViewing” (Orders_data) ]
                Close File [ “Antibodies” ]
                New Record/Request
                Go to Record/Request/Page [ Last ]
                Set Variable [ $GlobalData_Supplier; Value:Order_FieldSetters_Global::g_Supplier ]
                Set Variable [ $GlobalData_Retailer; Value:Order_FieldSetters_Global::g_Retailer ]
                Set Variable [ $GlobalData_orderedBy; Value:Order_FieldSetters_Global::g_OrderedBy ]
                Set Variable [ $GlobalData_Articlenumber; Value:Order_FieldSetters_Global::g_Articlenumber ]
                Set Field [ Orders_data::Producer; $GlobalData_Supplier ]
                Set Field [ Orders_data::Retailer; $GlobalData_Retailer ]
                Set Field [ Orders_data::Date_recordCreation; Get ( CurrentDate ) ]
                Set Field [ Orders_data::MatchField_Manual; $$Param_UniqueID ]
                Set Field [ Orders_data::Articlenumber; $GlobalData_Articlenumber ]
                Set Field [ Orders_data::OrderedBy_temp; $GlobalData_orderedBy ]
                Clear [ Order_FieldSetters_Global::g_Supplier ] [ Select ]
                Clear [ Order_FieldSetters_Global::g_Retailer ] [ Select ]
                Clear [ Order_FieldSetters_Global::g_Articlenumber ] [ Select ]
                Perform Script [ “PlaceOrder_simple”; Parameter: $$Param_UniqueID ]
                Open File [ “Antibodies” ]
                Perform Script [ “Ab_Refocus_1” from file: “Antibodies” ]
                Flush Cache to Disk

      Script: "PlaceOrder_simple"

           -> this is supposed to create a new record in the related table "OrdersDB_placedOrders"


           Set Variable [ $$MatchData; Value:Get(ScriptParameter) ]
           Go to Layout [ “OrdersDB_RecordViewing” (Orders_data) ]
           Show All Records
           Perform Find [ Specified Find Requests: Omit Records; Criteria: Orders_data::MatchField_Manual: “== $$MatchData” Omit Records; Criteria: Orders_data::MatchField: “== $$MatchData” ] [ Restore ]
           Show Omitted Only
           Go to Object [ Object Name: "OrderPortal" ]
           Go to Portal Row [ Select; Last ]
           Set Field [ OrdersDB_placedOrders::Articlenumber; Orders_data::Articlenumber ]
           Set Field [ OrdersDB_placedOrders::OrderedOn; Get ( CurrentDate ) ]
           Set Field [ OrdersDB_placedOrders::OrderedBy; Orders_data::OrderedBy_temp ]
           Go to Layout [ “OrdersDB_RecordViewing” (Orders_data) ]
           Show All Records
           The scripts are being called in the correct order. The values are being trafficked correctly.
           I ran it all a few times through the script debugger, and as far as I can tell, the values go where they should.
           Script parameters are being passed correctly.
           What does not work, is the script "Orders_CheckMatch". I should rather say: it does work, but always returns a FoundCount of zero, although a record exists where the defined field holds the value that is being passed into the script as a search reference.
           So what does also work is the creation of new records in the "recipient" database ("Orders").
           Please offer me some advice.
           I know that the overall design could be the problem. I have been working with filemaker for about three months now and I am only now learning how to handle it properly.
           I have set up the relationships so the creation of records is possible. I have attached a screenshot of the relationship graph of the "Orders" database.
           Thank you for your help, I appreciate all advice offered.


        • 1. Re: Problems with multi-file database solution

               You have not put your data into Files, you've put it in Tables. That's a big difference when it comes to managing your database design though transparent to the user in most details.

               I suggest that you change your data model. instead of a separate table for each type of item, create a unified table of all items that might be order of all types. Use fields in this table for:

               a unique identifier for each item
               The description
               The unit cost
               Any other data common to all items that might be ordered

               Then, if you need to, link in your separate item specific tables to this unified table of products to document details not common to all items that could be ordered.

               Your relationships would look like this:

               Orders----<Orders_Data>------Products-------<detail tables here

          • 2. Re: Problems with multi-file database solution

                 Thank you very much for answering.

                 I am currently rebuilding the solution in accordance with your proposed approach. I guess that I will have some more questions soon, so I would like to keep the post open still.

            • 3. Re: Problems with multi-file database solution

                   Hello again,

                   after some changes, I am still kind of stuck. I want to use a script to check whether a record exists that matches the contents of one field. If a record exists, I want to order the corresponding article. If it does not exist, I want to create it. I am using a script similar to the one specified above, called "Orders_CheckMatch". Whatever I do, although the values are passed correctly, the matching record is not found. This does not change however I pass the value from one script to the other: as parameter, or as the temporary value of a global field.

                   So I want script 1 from my file 1 to check, via the use of the "Orders_CheckMatch"-Script from file 2, if a matching record exists in file 2.

                   As I said, the values in the fields and/or variables are being passed correctly.

                   What do I have to look for when using variables in find requests?

                   The script is as follows:


              Set Variable [ $UniqueMatch_Value; Value:GetAsText(Items_available::ID_foreignMatch_temp) ]

              Set Error Capture [ On ]

              Allow User Abort [ Off ]

              Enter Find Mode [ ]

              Perform Find [ Specified Find Requests: Find Records; Criteria: Items_available::Unique_ID: “= $UniqueMatch_Value” ] [ Restore ]

              Exit Script [ Result: Get ( FoundCount ) ]


                   Changing the search operator to "==" does not have any effect. It always gives me error 401 although there is exactly one record holds the matching data in the field "Unique_ID".


                   Using Filemaker Pro 12 Advanced on Mac Os X 10.6.8


                   I have tried to change the data-model as suggested, but I am not entirely sure what to do now.

                   I think that the table "Orders_data" is not necessary for what I want to do.


                   All help is appreciated very much.


                   Best regards.

              • 4. Re: Problems with multi-file database solution

                     I suggest simplifying your design so that you do not need to have different scripts  in different files--that will greatly complicate the process of managing these scripts and keeping them up to date. If you use Manage | Database | Relationships to add occurrences of the tables from other files, you can set up a script to search the data in that table without needing to perform a script in another file.

                     Your script refers to a variable. If that variable is assigned a value in File 1 and then the script you have posted resides in file 2. The variable and its assigned value does not exist in file 2 and thus no records are found. (And thus the values are NOT being passed correctly.) You cannot use a variable to pass data from one file to another--which is one of reasons using a Tutorial: What are Table Occurrences? in File 1 that references the table from File 2 makes life simpler. To pass data from a script in File 1 to a script in File 2 would need to be done by passing that value as a script parameter.