6 Replies Latest reply on Mar 10, 2011 7:32 AM by DanielClark

    Refresh Portal Line Items



      Refresh Portal Line Items


      I am currently have a three tables: Purchase Orders, Line Items, and Parts Inventory. Each record in those tables has a unique identifier: kp_POID, kp_LineItemID, kp_PartID respectively. Additionally, the Line Items table has two dependent fields, kf_POID, and kf_PartID - which are related to the primary fields of the same name of the other tables.

      I currently have a portal on my Purchase Order layout that links to my Line Items. These line items are added via a script through a search bar and add button that searches the Parts Inventory table (for whatever was typed in the search bar), and then adds the desired result to the line items of that PO. It does this by going to the Line Items table, creating a new line item (new kp_LineItemID) and adding that Part to that line item's kf_PartID, via the part's kp_PartID, and finally adding the current PO's kp_POID to the kf_POID field (thus establishing a link between that line item and the PO in addition to the part).

      The line items table then has other fields that lookup that part's information from the Parts Inventory table (Manufacturer, Price, Quantity, etc) via that kp_PartID -> kf_PartID relationship. Finally, the script goes back to the original PO's layout and one sees the line item in the portal, with all of the desired information.

      This solution has been working wonders for me - however, I ran into a small problem here. If I find out later that something about that part isn't correct (say it was originally thought that the manufacturer was Microsoft, and I find out it was actually made by Apple) and I go and change the Manufacturer's ID in the Parts Inventory table, that line item from my PO still says the part was manufactured by Microsoft.

      This makes sense because the portal line item is a separate record from the Line Items table, but I would love to implement a "Refresh" button on my PO Layout. The basic idea would enable the user to fix some or many aspects of the part (except of course its unique kp_PartID) in the Parts Inventory, and then come back to that PO, hit "Refresh", and then have that line item update to the recently corrected manufacturer (or whatever it may be). I'm pretty sure this can be implemented, but I'm having trouble figuring out how FileMaker can do this. Can anybody help? Thanks.

        • 1. Re: Refresh Portal Line Items

          You can do this by hand or in a script.

          If the data is being looked up from Parts Inventory, You can trigger a relookup of that one line item by reentering/reselecting the kf_PartID field's value. By hand you can delete the last digit of the number and type it back in.

          In a script, you can use:

          Set Field [Line Items::kf_PartID ; Line Items::kf_PartID]
          Commit Record

          There's also a relookup field contents option in your records menu and also as a script step you can use, but it can relookup for all the records in your found set so this option could update more than you want to update.

          • 2. Re: Refresh Portal Line Items

            Ok, now will that refresh all of the line items on the current PO I'm on? Or just that one particular line item?? I would like to refresh all of the PO I'm on.

            • 3. Re: Refresh Portal Line Items

              That method refreshes a specific line item, but all the looked up value fields in that record that refer to the updated field as a key in the relationship.

              To update more than one record, that's where Relookup Field Contents comes into play. What is not clear from reading the help file is what will happen when you use this option for a portal record. In many cases, "found set" can be interpreted to refer to "all related records in portal" so it might update all the records in the portal, but you'll need to experiment. Please report back what you find out.

              If relookup can't be executed from a portal field, you can use Go TO Related records to bring the portal records up in a found set on another layout and you can perform the relookup there. Then switch back to the original layout. If you first freeze the window, this can take place invisibly to the user.

              One option to avoid all the need for relookups is to either discard the looked up value field and replace it with the actual field from the related table or to replace it with a calculation field that references the related field. Either way, changes to the product table would then update automatically. You wouldn't do that for a price field, but for informations such as a Manufacturer name or ID, that would probably be simplest.

              • 4. Re: Refresh Portal Line Items

                I tried the relook up field contents, and it replaces all of the line items not just shown in that portal, but of the entire table. I suppose what i can do is do your second option - search for the related line items in that PO (with my current kp_POID matching the kf_POID in Line Items), and then of those results, relook up the field contents of all of those. The question is - how do I script that? I have sort of an idea - and would that actually work?

                And in regards to your last suggestion - how would I implement that - then maybe I wouldn't need to have this refresh thing at all?? Thanks again.

                • 5. Re: Refresh Portal Line Items

                  The whole point of the last suggestion is to avoid needing any such refresh at all. In terms of Relational Database Theory. The same data should not be stored in more than one place except for relational key data needed for PrimaryKey to ForeignKey relationships. Looked up data is "different data" because it is generally used to capture a value that was current at a specific point in time such as the current item price at the moment it is purchased. Other data, such as your manufacturer name shouldn't be stored in two different tables in the first place for most database solutions I can think of.

                  If, In your portal row, you have a lineItem field MFGName that looks up this value from Products, enter layout mode. Double Click it. Select Products in the drop down then click the manufacturer name field to highilight it. You have now replaced the line item field with the matching data field in Products. Do this in every layout where you have this lineitems field and you can delete it from your lineitems table.

                  Here's a relookup script that should work for your lineitems should you really need it:

                  Freeze Window
                  If [not IsEmpty ( Line Items::kf_POID ) ]
                     Go To Related Record [Show only related records; From table: Line Items; Using layout: "Line Items" (Line Items)]
                     Relookup Field Contents [no dialog; Line Items::kf_PartID ]
                     Go To Layout [original layout]
                  End If

                  • 6. Re: Refresh Portal Line Items

                    Ok cool - thanks again!