Complex Lookup

[EFI]

Hello, I am wondering if there is a way to do a lookup that has to reference more than one source field to find a matching value.  For instance, I need to lookup a price of an item, but the item has various options such as size, thickness, and material type.  I need the price field on the related table's portal to lookup the item price based on whatever the user choses for the three options mentioned earlier.  I am not sure if there is a way to do this with the Lookup function in a script or if I have to approach it differently.

[Ocean West]

not with the lookup function but possibly with insert calculation or a script or even execute SQL

 

[comment]

It is not the lookup that is complex, it is the relationship. If you define it so that those three fields are match fields too (in addition to ItemID), there will not be a related record until the user fills all four of them and a matching record is found. And at that point you won't need the Lookup function or a script; a plain lookup will do.

 

[Ocean West]

instead of typing in a product code and other fields the other approach would be to display a picker or a selection wizard (in a popover or card window) where you drill down to the product to the item or sku that you desire if your products table don't have price variants for size or color or if they do you can figure out the math to add to or override the default options and then once selected along with quantity you script and add that record to your line item. 

[EFI]

39 minutes ago, comment said:

It is not the lookup that is complex, it is the relationship. If you define it so that those three fields are match fields too (in addition to ItemID), there will not be a related record until the user fills all four of them and a matching record is found. And at that point you won't need the Lookup function or a script; a plain lookup will do.

 

This worked perfectly.  Thanks!