Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Filemaker Pro 11 Scripting

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
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 …

808 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