conditional lookup?

[stefangs]

i have an invoicing scenario with line items. data into the line items is pulled from a supplies file via popup menus. the popup shows an ID and the literal, something like '123 Hourly charge'. so i enter 123 into the ID field, and the record looks up 'hourly charge' and enters the price, say 20. now all i need to enter is the quantity. so far so good.

in the supplies file i have specified a discount rate. when i enter a number greater than say 10, i would like the price field to fill with 18 instead of 20. right now i can only do this via a live unstored calc, because the lookup happens before i enter a quantity. obviously, i'd want a lookup instead of a calc so that old invoices don't change when i change a price in the supplies file. how can i automatically enter the price into a number field?

[Ugo DI LUCA]

Hi,

You may apply a second conditional trigger, but you'd need to script the record creation with an ending "Exit Record" script step.

Then, based on a Modifier calculation, you could dynamically alter the content of the price field based on the value in the Quantity field.

You need :

ModTime - autoenter Modification Time

c2ndTrigger = (ModTime*0)+serial

SelfModTriggerRel = LineItems:c2ndTrigger::LineItems:serial

StandardRel = LineItems:ProductID::Product:ProductID

QuantityBreak - lookup from "StandardRel"

cDiscountedPrice = Choose(QuantityOrdered>QuantityBreak, StandardRel::ListPrice, StandardRel::discountedPrice)

Now set your price field to lookup the value of the cDiscountedPrice through the "SelfModTriggerRel"

HTH

[stefangs]

thanks for the info - unfortunately, i can't quite get it to work. i assume that these fields must go into the line items file. what is the calculation function on the c2ndTrigger between ModTime for? doesn't that always evaluate to the same thing. maybe that's the point, but in my case the serial is a text field. would that still work?

i've gotten the QuantityBreak and discounted price or standard price to look up correctly, but the price is not being transferred/looked up to the line item price.

[Ugo DI LUCA]

Hi,

No problem with text Ids either. The only restriction, as I said, is that you need an exit script step.

Check this small attachment.

HTH

ConditionalLookup.zip

[stefangs]

hi and thanks so much for the template - that was very kind of you to put that together. i've got it working now, even with two preset discount levels, but i lost the ability to specify another price on the fly.

here's what i came up with: i defined a number field 'override' which is underneath the final price field which has allow entry disabled, so that clicking on it gets you to the override field instead. then, i modified the definition for cDiscountedPrice like this:

Case(IsEmpty(override), <original definition>, override)

now the final price will look up something from the product supplies file or the override price. doesn't feel very elegant, but i couldn't think of a way to do this without the extra field. good enough, huh?