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!!!