?
Solved

Filemaker Pro 11 Scripting

Posted on 2011-02-15
5
Medium Priority
?
508 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 1000 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 1000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

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…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

777 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