Solved

Filemaker Pro 11 Scripting

Posted on 2011-02-15
5
486 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 25

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 25

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 25

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FileMaker 13 WebDirect - Tab Label text cutoff in browser 7 766
FM Exporting Pictures In Container 1 1,010
FM 13 Get Field Name Comments 1 274
FileMaker Pro: How to change fields 4 106
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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