3 Replies Latest reply on Jun 7, 2013 7:04 AM by philmodjunk

    Complicate Find and update portal rows

    KenCheung

      Title

      Complicate Find and update portal rows

      Post

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

           Ken

        • 1. Re: Complicate Find and update portal rows
          philmodjunk

               Actually, there is SQL in FileMaker 12 if you use the ExecuteSQL () function and that could be used to look up the correct price from your pricing schedule.

               Without using execute SQL--as is needed in FileMaker 11 or older versions, I'd modify your relationships a bit:

               Current Price-----<Sales Order LIne Item>---------Products

               Current Price::fk Product ID = Sales Order LIne Item::fk Product ID AND
               Current Price::Effective Date < Sales Order LIne Item::CreationDate

               Sales ORder Line Item::fk Product ID = Products::ID --> this is a change in your existing relationships.

               Current Price would be a new table occurrence of Product Price Schedule and Current Price would be sorted by Effective Date in Descending order so that the first related record is the current price that is in effect for that line item.

          • 2. Re: Complicate Find and update portal rows
            KenCheung

                 Dear PhilModJunk,

                 Thanks for your help, but I didn't understand what is "table occurrence" ( Today's my 6th day working on FileMaker Pro sad ).

                 Furthermore, how can I add "sorting criteria" on a "table" ? I know how to make it in Layout but not Table.

                  

                 Many Thanks for your kindly help.

                  

                 Ken

            • 3. Re: Complicate Find and update portal rows
              philmodjunk

                   A Tutorial: What are Table Occurrences? is one of the boxes found in Manage | Database | Relationships that you connect in relationships to other table occurrence boxes. FileMaker automatically creates one table occurrence with an identical name each time you add a table to your file, but you can create as many table occurrences for a given table as you need.

                   The sort order is not added to the table, but can be added to a relationship for a specified table. Open Mange | Database | Relationships and double click the line linking two table occurrences. You'll get a dialog, where you can specify a number of relationship details--including specifying a sort order for either of the two tables linked in that relationship.