AnsweredAssumed Answered

Problems with multi-file database solution

Question asked by Greymalkin on Mar 11, 2014
Latest reply on Apr 3, 2014 by philmodjunk


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.