Defining relationships between agreement terms and products with And / Or

[mantooth29]

This thread was also posted here. I got what I believe to be a workable solution, but I was hoping to get some feedback regarding my proposed implementation but also alternative suggestions if anyone has them.

I will be designing a database over the next few months, and there is one problem on the horizon that I think will be a significant challenge.


How to track relationships between agreement terms and products using And / Or conditionals.
I need to be able to accurately model the agreements a customer has on various products, and whether the agreements are concurrent or disjointed.


Some things to keep in mind

1) A single customer could have multiple agreements, covering various products.

2) A single agreement can look something like this (although they get much more complex):


5 of Product_A AND
10 of Product_B OR
50 of Product_C OR
$500 of Product_A


So I am thinking of a table that models the deal's structure like so:

AgreementID	Product	Line	PreceedingLine	TrailingOperator	Count	Cash
55555	Prod_A	1	0	AND	5	NULL
55555	Prod_B	2	1	OR	10	NULL
55555	Prod_C	3	2	OR	50	NULL
55555	Prod_A	4	3	NULL	NULL	500

I feel like I am introducing some redundancy here, ie if one agreement line gets removed from the deal, all the PreceedingLine records will have to be updated along with the Line records.
But this is the only way I can think to accurately model this kind of data.

Anyone have a better idea? Any alternative suggestions, or ways to improve this design would be greatly appreciated!

[rpeare]

Do you have standard sets of 'rules'. Or can you have rules on any quantity of any product or any cash amount?

Are you looking to perform mathematical functions with the rules or is it strictly for re-printing the agreements/products in a report format? In other words is it your intent, based on your rules, to see if a client has met or failed to meet the terms of their agreement?

If it's strictly for re-printing terms that's fairly easy and what you've laid out should work (though I would change 'preceedingline' to 'sortorder' since that's what it is)

You could take it a little deeper though and instead of having a sort order reference another rule line if it's an "AND" statement or the inner part of an "OR" statement

For instance your original statement

5 of Product_A AND
10 of Product_B OR
50 of Product_C OR
$500 of Product_A

could be read as:

5 of A AND (10 of B OR 50 of C OR 500$ of A)
or
(5 of A AND 10 of B) OR (50 of C OR 500$ of A)
or
(5 of A AND 10 of B) OR (50 of C) OR (500$ of A)

and so on.

if you rebuilt your table so that additional or or and statements referenced the original line they were relevant to it might be easier to put back together.

AgreementID	ProductID	PK	Rule	RuleLink	TrailingOperator	Count	Cash
55555	1	1	1		AND	5
55555	2	2	3	1		10
55555	3	3	2		OR	50
55555	1	4	4	2			500

In this example it would be equivalent to:
(5 of A AND 10 of B) OR (50 of C OR 500$ of A)

Rule 1 is a combination of PK 1 and PK 3 with the AND join
Rule 2 is a combination of PK 2 and PK 4 with an OR join

if your rules get more complex you'd have to have a reliable way to notate them in your table.

But then I'd recommend you ad a rule number (the line number being an autonumber pk field I would assume) you'd need a rule number as a label

[mantooth29]

Yes you nailed it. My intent is to reprint, verify, and provide a UI display of these agreements which are all fed from my table. No calculations will be required.

There aren't any rules unfortunately. It's basically the Wild West here, and as of now the data feeds do only a mediocre job of accurately modeling the business activities.

So an agreement could actually be (5 of A AND 10 of B) OR (10 of A and $500 of C).

One of my biggest concerns was how easily I could put these back together in a form / UI.

Trying to wrap my brain around evaluating PK-Rule-RuleLink, but it seems like that would give me the coordinates of the record before and after the active record. Correct?

Thanks so much for the reply.

[rpeare]

Here's a quick example:


mantooth29.zip

Just open the form, select an agreement then click the button you can look at the tables for the setup and the ON OPEN event of the report to show how I built the control source for the txtRules on the report.

[orange]

I read the thread but have not looked at any links.
I see the issue as "How to set up the ANDs and ORs" -- at least that's an initial issue.
Research BEDMAS ---- order of operations.
Good luck.