Solved

Filemaker Pro 11 Scripting

Posted on 2011-02-15
5
504 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
[X]
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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

687 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