3 Replies Latest reply on Jan 4, 2016 5:05 AM by mikebeargie

    Comparing Records From the Same Field.


      Say there is a "Company" field with 5 records: Apple, Orange, Banana, Kale, and Broccoli. I want a layout with two drop down lists, where the user gets to pick two of the 5 companies to compare data with. Eg, average spendings of Apple company vs. Banana company, who the distributors are, and average overages of Apple and Banana company, etc. All of these information come from their respective fields. Is there a type of script trigger that I can use so that the data within the layout is displayed based on the two separate companies that I am choosing from?

        • 1. Re: Comparing Records From the Same Field.

          The easiest method would be to open two windows, side by side, with a form view of each record to compare. Filemaker has window controls via script that can automatically open and size windows in this fashion.


          A middle of the road method would be to use a dummy "report" table that relates to "company 1" and "company 2" tables respectively, and shows the related fields from each of the two tables side by side, on a "report" layout. This requires you to add a table, plus two additional table occurrences of "company". You would then use a value list to select the key relating "report" to company 1 and 2, and the related data will show via the relationship.


          An even more difficult to setup, but not requiring any of the window voodoo or setting up more schema would be a method called "virtual reporting", in which you use a script to gather data into a global variable (EG $$company1), and then use a reports table that uses unstored calcs to grab values out of the variable (EG report::company1_field1 = GetValue($$company1;1) | report::company2_field1 = GetValue($$company2;1) | etc..)

          If you google "filemaker virtual list technique", you will find many resources explaining this process.

          • 2. Re: Comparing Records From the Same Field.

            Thanks! I think I will try out the first option -- I've tried the second before but since there were a lot of records it got kind of confusing to execute. How exactly do you open two windows via script?