4 Replies Latest reply on Jun 16, 2010 10:05 AM by tony24601

    Comparing data from two records



      Comparing data from two records


      Hi, I was wondering if something like this is possible, since I couldn't seem to find a way to do it. I have a list of line items from a purchase report, and each of those items is associated with a particular order number. Each item being purchased needs to be categorized, and the user can select a category from a list. The thing is, I want the category to autocomplete as the user enters in the purchased line items so that, once they select a category for one item in the list the next item will have the same category (and they can change it if need be). So basically, the code I want does this:

      autofill by calculation:

      if the purchase number for new line item is the same as the purchase number for the last line item

          current line item gets the same category as the previouse line item

      otherwise do nothing (since it means its the first item being listed in this order)


      or in a slightly code-ier way:

      if(newLineItem.purchaseID == previousLineItem.purchaseID)

           newLineItem.category = oldLineItem.category;


      My problem is that I don't know any way to get the purchaseID value (or category value, or any other value) so that I can compare it to the new item's value. Any ideas? (sorry if that was wordy).



        • 1. Re: Comparing data from two records

          What version of filemaker are you using? (Filemaker 10 and 11 support script triggers and they might be an option here).


          How is your layout for this designed? Is there a portal? How are the tables related? etc?


          There are ways to do this, but if we knew more about how you've designed your database, it'd be easier to describe how to set this up.

          • 2. Re: Comparing data from two records

            Ah, sorry. I'm using Filemaker 11.


            My current layout is one where you enter in an order you've made. And part of that is that there is a portal, which contains all the items that belong to the order. So what I'd like is, you enter in the first item (via the portal), and select the category. And then for the rest of the items you enter in, the category gets set automatically to the category of the previous item (so if you select a different category for item 3, item 4 will by default have the same category as item 3). The tables are related by the ID number of the order by the way.


            Hopefully that clears it up a bit. If anything wasn't clear, let me know and I'll try to expand a bit. And thanks for the reply.



            • 3. Re: Comparing data from two records

              So you have two tables involved, I'll call them Orders and LineItems.


              You have a layout that specifies "Orders" in layout setup... and a portal on that layout to LineItems linked by PurchaseID--hopefully an auto-entered serial number.


              Define a field in Orders, as Category. You can place this field on your layout so that a user can select a value in it or you can leave it off the layout and simply let triggered scripts interact with it.


              In your lineitem table, set up LineItem::Category as a looked up value that looks up a value from Orders::Category. (This is a field option on the auto-enter tab.)


              That's all you have to have here, but it would require placing the Orders::Category field on your layout and you user would have to select different categories from time to time while filling in your order form. The following script trigger will update Orders::Category with any newly selected value in LineItems::Category.


              Make a one line script:

              Set FIeld [ Orders::Category ; LineItems::Category ]


              And setup up an OnObjectExit Script trigger on the LineItems::Category field to run this script.

              • 4. Re: Comparing data from two records

                Thanks a lot, that worked perfectly.