Hello,
I have a details page that uses Spreadsheet mode. One of the inline editable fields is a lookup. This lookup is dependent on one field from the master table and one of the other fields in the details table. Normally I would use the "Where" part of the lookup field to filter by the master table (like LocationID = ':master.LocationID'), and use the "This dropdown is dependent on..." feature of the lookup and set it like "Parent field = ItemID, Filter field = ItemID".
This works fine on an Edit page, but this is a list page in spreadsheet mode, and the "ItemID" field is not shown on the list page (it's also not a lookup field), so the dependent lookup feature doesn't seem like the right approach. What I'm looking for is something like this added to the "Where" of the lookup field:
LocationID = ':master.LocationID' AND ItemID = 'ItemIDFromTheSameRowOfThisGridRecord'
Is there a way to get another field from the same record to use in the where statement of a lookup field? Or is there a better approach to achieve what I'm trying to do?
Hopefully this question makes sense. If it does, stop reading. But here is some more info about what I'm trying to do:
Master Table
OrderID
Location
Details Table
ID
OrderID
VendorID
ItemID
Lookup Table
Location
VendorID
ItemID
Each location can order the same items from several different vendors. When an order is created it is tied to a location, and all items available for that location are added to the order (details table), but only one instance of each item, and the vendor for that item is the one that has the lowest cost. Adding the items is achieved through a stored procedure. After the order is created, the user is redirected to the list of items, and that list is in spreadsheet mode. I need to show a dropdown list of all vendors that can offer that item to the location that created the order. This allows the user to choose a different vendor.
So what I need to do in the lookup of the VendorID field of the details table is show a list of VendorID's from the lookup table where the location = the location from the master table and the ItemID = ItemID of the record I am on.
Thanks,
Tim