Complicating a relationship

[sbg2]

Typical Order entry database with Orders, Order Line Items, Company (Customer) and such. Up until this point each customer could be associated with 0 to 1 Reps and the Rep was paid a set % at the order level. Recently things have changed slightly:

1) A customer may be represented by more than 1 Rep

2) The percent a Rep receives can change, a change in percent should be reflected on future orders only, older orders should not be affected.

3) The percent a Rep receives should be editable at the order level to account for 1 off deals.

I have attached a sample database that shows the basic structure I started for adding more than one rep to a company. My issue is how to implement item # 2 and 3 in the above list.

Any help appreciated.

Company.zip

[sbg2]

Attached the same file compressed with StuffIt.

[LaRetta]

Hi sbg2,

I have the same situation.

2) The percent a Rep receives can change, a change in percent should be reflected on future orders only, older orders should not be affected.

Just write the selected Rep and % commission directly within the Order. Create standard text and number fields then use Set Field[] script-steps (attach script directly to the portal field to grab their value). Disallow entry into field via Field Behavior.

3) The percent a Rep receives should be editable at the order level to account for 1 off deals.

If written within the Order, it will be editable at the order level. Protect from Users changing anything Order-specific which is written to General Ledger. Also protect from changes which may confuse a Customer if you refer back to the Order later.

The only complications in your relationship might be simply because you should have more than one table. Things would be much easier for you.

LaRetta

[sbg2]

If written within the Order, it will be editable at the order level. Protect from Users changing anything Order-specific which is written to General Ledger. Also protect from changes which may confuse a Customer if you refer back to the Order later.

The only complications in your relationship might be simply because you should have more than one table. Things would be much easier for you.

LaRetta

It does seem logical to store the rep information with the order but how? For example in the file I attached to my original post if the company Acme Acres will be represented on all future orders by Broken Brokerage at 10%, Remmy the Rep at 2% and Joe Schmoe at 1% how do I store that information at the order level? A repeating Rep field and a repeating CommissionPercent field? Or a Rep field that = Broken Brokerage

[LaRetta]

I suggest a Commissions table because you will be referring to, and summarizing on, these Commissions. So instead of writing to the Order itself, write to the Commissions table (which will 'freeze in time' these sales).

Since one Order can have many Reps and one Rep can have many orders, this is called a many-to-many join. Fields would be: OrderID, RepID and % Commission. I would also include: DollarsPaid and DatePaid. DollarsPaid could be pulled from the Order based upon %; however, since you will be writing to the Commissions table anyway, it's easy to write DollarsPaid at the same time. This will save time down the road so you aren't displaying aggregate data (which can slow things down). Most of this can be handled by lookups.

I do not use repetitions for data. There may be times when it is appropriate but I don't think this is an example of one. As you've seen, repetitions here will complicate your process greatly. Now ... having said that, if you still want to keep repetitions, someone else will need to assist on pulling and writing the data to Orders. I simply don't use them enough to be confident in my suggestions here. I could give you script to write this to multiline in Orders, but not repetitions.

LaRetta

[sbg2]

I'm still making no headway with this. I have added a new table Commissions (file attached) but have no idea how or where to relate it? Further once I relate the Commission table the Rep information would preferably be auto-entered into the Commission table when an ID_Company is defined at the Order level. Is there a way to auto-enter more than one record into a related table?

If I were to do this "manually" I would do the following:

- once an ID_Company is defined in the Order table I would want to import all records from a found set of ID_Company = ID_Company from the Company~Reps table.

- For each new record in the Commission table the ID_Order would be set to the current ID_Order.

Commission.zip

[sbg2]

Well I fumbled through it myself and the attached is what I came up with (Order.fp7 is the main file). In order to get more than the one record into the related table I did a find on the Company~Rep file and imported into the commission table. Had to create a seperate layout to enter a new record and pause so I can trigger the script, not a big fan of the Pause/Resume script step.

At any rate, it appears the rep information will now be "auto-entered" into the commision table and that the information is editable at the order level.

Reps.zip