How do I make a conditional lookup?

[raymanj]

Hi,

I am trying to add on to my current invoicing solution. Right now I have 4 main files that enter act with this invoicing solution:

[*]Customer.fp5

[*]Sales.fp5 (holds the invoice records)

[*]LineItems.fp5 (items added to the invoice)

[*]Inventory.fp5 (looksup item prices and descriptions when a stock number is entered into a lineitem filed of the invoice.)

Problem

We sometimes give special pricing, on some items, to certain customers. So the only way for an employee to know if this customer has a different price for this item is to look at past invoices. Scan through them looking for that item and the price on the invoice. As you can see, this has caused a lot of waisted time per item being added to an invoice. To make things worse, sometimes employees miss the discounted price on prior invoices and over charges. Which causes more wasted time.

My idea

My idea to solve this was to create another file (CustomerPricing.fp5) that would hold any special prices for that customer for any item in Inventory.fp5. So when an item number was typed into the invoice portal to LineItems.fp5, the lookup would look there first to see if a record exist for this item for that customer. If so then that price would be used, if not then it would use the price from the Inventory.fp5 file.

The Road Block

Now relating back to the subject of this post, how do I do a conditional lookup in Filemaker? How do I get FM to first lookup in the Customerpricing.fp5 file then if needed to look in the Inventory.fp5 file?

An ideas would be very help.

[Ugo DI LUCA]

Check this thread

[John Caballero]

You're on the money with the CustomerPricing join file. FileMaker can only do one lookup, but that's not a problem you need to worry about.

In your CustomerPricing join file create a concantenation of your customer ID and your product ID. (A text calculation that looks like CustomerID & " " & ProductID.)

Presumably LineItems.fp5 has a CustomerID field (if not, add one and have it get its value from Sales.fp5 at the time when the line item is created). Create a similar concantenation in LineItems.fp5 (again, CustomerID & " " & ProductID). Create a relationship from LineItems.fp5 to CustomerPricing.fp5 based on the concantenated calc. Create a "Special Price" field in LineItem.fp5 that looks up the special price value in CustomerPricing.fp5 based on the concantenated calc. relationship. The special price value will get looked up when you enter both the customer ID (which will probably happen as soon as the line item is created) and the product ID.

[Jim McKee]

Lookups can't be conditional to the extent that they are able to branch to two different files based on some parameter.

If your line item entry process is scripted:

Create a relationship in LineItems ("item_customer") between:

c_itemcode_customerID (in LineItems) and c_itemcode_customerID (in CustomerPricing)

then:

Set Field [unitprice,

Case(

IsValid(item_customer::constant), unitprice (from CustomerPricing),

unitprice (from Inventory)

)

]

If your line item entry process is not scripted, create a calculation (unstored) field ("c_unitprice") which follows the same logic, i.e., if there is a related "item_customer" record in CustomerPricing, return that price ... if not, return the value in unitprice, which is looked-up from Inventory. Then:

ExtendedPrice = Extension * c_unitprice,

because c_unitprice will always hold either the special price from CustomerPricing, or the looked-up value from Inventory.

You can overlay the unitprice field with the c_unitprice field. Make the c_unitprice calc field opaque and remove it from the tab-order. Leave "unitprice" in the tab order. The user will then be able to click on the overlaying calc field to access "unitprice" or tab into it.

See the small attached demo file which mimics a relational system using the above structure.

customerpricing.zip

[Ugo DI LUCA]

Yes, you can have 2 separate lookup fields and a calculated result that checks which one is empty. You could even have a final price lookup this unstored calculation for indexing purpose.

A Pricing "strategy" can though lead to some tricky settings.

In some businesses, the parameters could be far more involved than this single case. The link I provided has a test file with some separate tables, all of them being one possible lookup table.

As a wholesaler/retailer, I currently have not less than 15 "conditions", part of them being splitted into separate files. The Test file I provided in the link only holds a few of them. I hope it's clear enough.

Once all lookups are in place, the big deal is to identify which one to use, depending on some "preferences ranking", not ot mention the option of overuling it.

Here are some (non exhaustive list) scenarios that could happen

A price could vary :

- if the product is available in stock

- if a Promotional price (and period) has been defined

- if a Special Price was defined for some customers

- if a Special Price was defined for some customers AND affair

- if the quantity ordered reaches some levels, leading to multiple price breaks that could in turn be conditionally affiliated to some customer list

- if the product is sold as part of set, along with other products,...

Other parameters :

1. When defining a Promotional price, depending upon either the Product or Customer :

- All other breaks and rebates could be locked

-The Special Price could apply even if it is higher than the Promotional price.

- The best price could be used

2. Depending upon either the Product, Stock availability and Customer Type, the Quantity Breaks could be calculated over a set of orders, by adding each quantity.

Simplifying it, as an illustration, say for Product X,

List Price : 20.00 $

Promotional Price : Period - Active - 18.00$

Special Prices : Customer A: 15.00 $ - Customer B: 13.00 $

Special Prices/Affair : Customer A for Affair Project Y: 12.50 $

Standard Qty Breaks : > 50units [10%] >100 [20%] >200 [30%]

Custom Qty Breaks : > 50units Customer C [15%]... >200 Customer A [30%]

So except for some Customers, if a customer buys more than 200,00 units, the promotional price could be higher or not.

If Customer A purhases more than 200 units, he could get a price lower than his given Special Price, for any other affair than Project Y

The good notice is that FM is perfect tool to handle these multiple criterias.

[raymanj]

Well, I have tried all your suggestions and each one of them have come very close. I even tried another suggestion from another thread, but it was missing something I needed too.

The problem that I am running into to get this solution to work is to have the LineItems.fp5 do a self lookup on the same record as it is being entered. Well Filemaker cannot do this as the record is only in memory and has not been committed, therefore no data to lookup.

Let me explain what I am trying to do in more detail. You can get the over all idea and layout from my above post.

In my LineItems.fp5 file I have these major fields:

• 
  
• List Price (lookup from Inventory.fp5) static field
  
• Special Price (lookup from CustomerPricing.fp5 based on relation of ItemID_CustomerID) static field
  
• cPrice (a calc field = one of the above fields depending on their priority)
  
• Override Price (used by user to enter a different price) static field
  

Now, I wanted the 'Override Price' field to do a self lookup on the 'cPrice' field at record entry. The 'override price' field is the field that shows up on the portal in the Sales.fp5 file where all user data entry is done. This self lookup would show the correct price, allow users to change it just by TABbing in, and be a static field for faster calculations.

I can't have the final price field be a calculation, because I still need to do other calculations like invoice discounts and separate line item discounts. Then multiply that by the quantity for a final extended price. then sum all those extended prices and there could be 70 or more line items to sum. After all that do taxes, shipping, payments, credits, etc.

I am getting worried that adding in this new calculation so far up the chain will kill user performance.

I may have to go back to my original setup without the 'special price' field. I have that working great with all the features above except the special price per customer section.

Ugo DI LUCA said:

Yes, you can have 2 separate lookup fields and a calculated result that checks which one is empty. You could even have a final price lookup this unstored calculation for indexing purpose.

A Pricing "strategy" can though lead to some tricky settings.

In some businesses, the parameters could be far more involved than this single case. The link I provided has a test file with some separate tables, all of them being one possible lookup table.

As a wholesaler/retailer, I currently have not less than 15 "conditions", part of them being splitted into separate files. The Test file I provided in the link only holds a few of them. I hope it's clear enough.

Once all lookups are in place, the big deal is to identify which one to use, depending on some "preferences ranking", not ot mention the option of overuling it.

Ugo, it seems you have accomplished what I need and more with lookups. Do you have many line items per invoice?

Jim McKee said:

You can overlay the unitprice field with the c_unitprice field. Make the c_unitprice calc field opaque and remove it from the tab-order. Leave "unitprice" in the tab order. The user will then be able to click on the overlaying calc field to access "unitprice" or tab into it.

I tried this but the overlaying of the 'override price' field with the 'cPrice' field gives some unexpected user drawbacks. When TABbing through the portal, upon entering the 'override price' field, which is covered by the 'cPrice' field, the price showing on the screen would disappear from the users sight. Even though the data is still there, to the unexperienced user, they become confused as the price seems gone. So they will be tempted to re-enter it into the 'override price' field. This is not needed.

But this may be my only answer.

Thanks for all the help and I will keep trying.

[Ugo DI LUCA]

Ray,

I can't evaluate how far you've been into this development, nor how long you've been playing with FileMaker Pro.

There is though something of primary importance in my opinion.

Even if it is more involved, a scripted method can be safer and quicker The files I've thrown in this attachment could eventually be used as it comes. However, the main purpose was to show how you could bring back from external tables any data from a relationship.

You'd probably agree that there's no need to have all these fields present in a Line Item. All we need to have is

- a List Price

- the discount rate

- the Final Price

- some "why" field that would let you know from which process this price was brought (Promotional, Special, QuantityBreak,..., Over-Ride by user#)

Therefore, a scripted method would be just fine enough here. You'd just set the result of the price be scripted using If steps rather than Case functions.

If, for some reasons, your line items were affiliated through a portal set to "allow creation of related records", I'd suggest that, when you're done with the data structure and all relationships are in place, that you substitute some steps involving scripts.

Relational design is way cool. Implementing it as something 100% natural can become very messy and disappointing in term of performance. Not withstanding the fact that FM did a great job in letting us use these portals with Auto-creation, a scripted method can be used for may other purpose (checking privilieges, disallow record creation on locked invoices, ...).

If you had created a related Invoice Item by a scripted process, using some global fields for the Item_ID, and ended your script with an Exit Record step, then as soon as you will fill the Quantity field, the n_Overide field could be lookup using the Modification Time.

I'd just use Bob's terminology and method here, but you may have a look to Ray's article here on the Forum too.

So

h_ModTime = Hour, ModificationTime auto-entered

serial = num, your line item serial

SJLookupMod => A Self-Relationship from c_trigger to the serial field

n_overide (lookup from SJLookupMod::c_Price

This is a very small approach of how an item would be entered in my line item, and yes currently I have plenty of items and quantities too in my invoices.

If you need more help, just ask.