Solved

Filemaker Pro 11 Scripting

Posted on 2011-02-15
5
474 Views
Last Modified: 2012-08-13
I have a table called 'SELLER' (it has four fields: 'SELLER','ITEM_ID', 'PRICE', and ‘SELLER_SELECTOR'). There are two occurrences of the 'SELLER' table (‘SELLER’, 'SELLER_ITEM'). What I am trying to do is to have a script (I have one, but not working - 'Select Lowest Seller By Item') that will go through my table 'SELLER' and based on the 'ITEM_ID' field set the 'SELLER_SELECTOR' field to '1' for the items that have the lowest price. If two or more items (depending on the number of items that have the same item_id) have the same price, then nothing should happen.

See attached for sample of my database. Also see below, for a more graphical explanation of the above.


ITEM_ID            PRICE            SELLER_SELECTOR        
A123            5.00            
A123            5.00      
B991            3.00
B991            2.00            1            
B991            4.00            
B777            9.00            1
B444            4.50            1


Any assistance will be greatly appreciated.
SELECTOR.fp7
0
Comment
Question by:PachecoPrimo
  • 3
5 Comments
 
LVL 24

Expert Comment

by:Will Loving
ID: 34902076
There is really no reason to use a script or script trigger here, it can all be handled by a calculation field and a second table occurrence.

1) First, I changed your "Price" field to a Number field - very important in terms of sorting.

2) I added a second Table Occurrence called Seller_Item_Price and created a relationship between it and seller using both the Item_ID and the price. With this relationship I can test to see how many records there are for that item that have the same price.

3) I added the following calculation field

Seller_Lowest = [ Case( SELLER = SELLER_ITEM::SELLER  and Count( SELLER_ITEM_Price::ITEM_ID ) = 1 ; 1 ) ]

The first part of this calculation checks to see if the Seller for the current record is the same as the Seller for the lowest price record. Because the original relationship between Seller and Seller_ITem is sorted by Price (and because I changed Price to Number field) the first related record will always be the lowest price.

The second part of the calculation uses the new relationship to count whether there is more than one record for that item that has that same price. So, if the ID matches AND there is in only one record at that price, then the Seller_Lowest field gets the value of "1".
SELECTOR-r2.fp7
0
 
LVL 12

Assisted Solution

by:North2Alaska
North2Alaska earned 250 total points
ID: 34902104
No script necessary.  Just use a calculation and a relationship.  I have modified your example...
SELECTOR.fp7
0
 

Author Comment

by:PachecoPrimo
ID: 34903193
Hello North2Alaska/willmcn,
I actually wanted to use a script because I do not want this field to be a calculated field. The reason for this, is that I want eventually the user to set this value manually (via using a button to set to "1"). I believe you can not enter data into a calculated field. This is the reason that I wanted to use a script and also for me to get more experience with scripting.

0
 
LVL 24

Accepted Solution

by:
Will Loving earned 250 total points
ID: 34903330
There are a lot of ways to do this including using the calc field plus an additional override field for when your user sets the preferred seller manually, but since you want to do it with a script, I've revised the example file to include one method that will yield the results you are looking for. There are likely ways to do it with fewer lines of code but this gets the job done.
SELECTOR-r3.fp7
0
 
LVL 24

Expert Comment

by:Will Loving
ID: 34903528
The attached r4 file includes a simplified script, just four steps and removes some unnecessary code from the Replace command. This marks the first record as selected. You'll need to add a little bit of additional structure to determine if there is more than one record with the same low price and therefore the script should skip marking any record. The current steps are:

 Script steps
Because the relationship is sorted by price, the GTRR (go to related records) step automatically puts the lowest price first, therefore the Replace command simply marks the first record with 1 and clears the rest.
SELECTOR-r4.fp7
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now