AnsweredAssumed Answered

Complicate Find and update portal rows

Question asked by KenCheung on Jun 6, 2013
Latest reply on Jun 7, 2013 by philmodjunk


Complicate Find and update portal rows


     Hi, a newbie to call for help.

     First of all, background of the data: I have a product table and price schedule with a "effective date" s.t. the price of each product will be effective upon the effective date. I have create two tables and a relationship like a product record like this:


     Then I have a sales order form and line items, like this:

     So an entry form is made like this:

     Of course I have link the Model No between Products and Sales Order line item tables s.t. I can have a Value List for the Model No in the portal, and a "Lookup" for the Product Name.

     Now I have to search the Product Price Schedule for this particular model's Price according to the Sales Order date and Effective Date. In SQL, I'll have something like:

SELECT PPS.Price FROM Products AS P JOIN [Product Price Schedule] AS PPS ON P.ID = PPS.[fk Product ID] WHERE P.[Model No] = ? AND PPS.[Effective Date] <= ? ORDER BY PPS.[Effective Date] DESC LIMIT 1;

     where the execution parameters will be the Model No in the portal line and the SO Date.

     There's no SQL or VIEW in FileMaker, and no ODBC for ExecuteSQL because I want it to go onto iPad.

     To do this when [Model No].OnObjectValidate() I have written a script that use a layout with the product prices, Enter Find Mode [], set the search fields, Perform Find [], Sort Records, and Exit Script with the value from Price field. Then I Go to another Layout of the Sales Order line item, find the record using the ID value, and Set the Unit Price field. This works for ONE record.

     HOWEVER, when I change the SO Date (which is needed) I cannot use this technique to iterate through the records in the portal using "Go to Portal Row" ; "Loop" because whenever I jump back from other Layout (i.e. Go to Layout [original layout]) the portal row loop get back to the first row, and the script run in infinite loop.


     A long question, sorry about that. Any suggestion? Many Thanks!!!