Hello All, 

Newbie here.  My question relates to insuring referential integrity among some custom objects.  I have created custom objects:

OrderItem //a detail on an Order (participates as such in a Master/Detail relationship to Order.)  OrderItem refers to a Product, and a quantity ordered for some requestedDate.  Ultimately, upon fulfillment it will be updated to reflect date fulfilled, quantity variances and the like.

Authorization //an independently persistent object that refers to an Account and a Product and contains a StartDate (and optionally an EndDate).  AProduct is said to be authorized at anAccount on aDate IFF there exists an Authorization referring to that Account and Product with a StartDate <=  aDate <= EndDate.

When constructing an Order (for some Account), one writes some number of OrderItems - each relating to some Product.

The referential integrity issue is that to be valid, an OrderItem should only refer to a Product which is Authorized for the Account on the OrderItem.requestedDate.

Now the easy way to make this work would be to Filter the list of Products shown to the user at OrderItem creation time.  But the filter functionality doesn't seem to be available for lookup items, only master/detail lookups.  Maybe this is due to the Beta status of the functionality, or maybe it has to do with my level of account - Not sure.

If someone can tell me how to get the filter working so that only valid choices are offered, that would be great.

But failing the capacity to filter, then I'd like to Validate the OrderItem against the Authorizations for the Account.  

For extra credit, there's also the fact that the set of Authorizations for an Account is actually the transitive closure of all Authorizations for that Account and it's Parent (recursively to it's highest level Parent).  

So - if someone has any words of wisdom (or even better) example code, I'd be grateful.  Especially if there's some way to make this work without getting into APEX and so on.  I'm a business level user.

Thoughts?

Thanks all,

SandwichMaven

Hello All, 

Newbie here.  My question relates to insuring referential integrity among some custom objects.  I have created custom objects:

OrderItem //a detail on an Order (participates as such in a Master/Detail relationship to Order.)  OrderItem refers to a Product, and a quantity ordered for some requestedDate.  Ultimately, upon fulfillment it will be updated to reflect date fulfilled, quantity variances and the like.

Authorization //an independently persistent object that refers to an Account and a Product and contains a StartDate (and optionally an EndDate).  AProduct is said to be authorized at anAccount on aDate IFF there exists an Authorization referring to that Account and Product with a StartDate <=  aDate <= EndDate.

When constructing an Order (for some Account), one writes some number of OrderItems - each relating to some Product.

The referential integrity issue is that to be valid, an OrderItem should only refer to a Product which is Authorized for the Account on the OrderItem.requestedDate.

Now the easy way to make this work would be to Filter the list of Products shown to the user at OrderItem creation time.  But the filter functionality doesn't seem to be available for lookup items, only master/detail lookups.  Maybe this is due to the Beta status of the functionality, or maybe it has to do with my level of account - Not sure.

If someone can tell me how to get the filter working so that only valid choices are offered, that would be great.

But failing the capacity to filter, then I'd like to Validate the OrderItem against the Authorizations for the Account.  

For extra credit, there's also the fact that the set of Authorizations for an Account is actually the transitive closure of all Authorizations for that Account and it's Parent (recursively to it's highest level Parent).  

So - if someone has any words of wisdom (or even better) example code, I'd be grateful.  Especially if there's some way to make this work without getting into APEX and so on.  I'm a business level user.

Thoughts?

Thanks all,

SandwichMaven