Repopulate Foreign Key

[Spac3man]

If a user wanted to edit an 'order' and change a field that was part of a relationship definition, how can I get the _fk_ID to repopulate in the related table?

SPECIFICALLY: if order 'ORD2010126' is later edited in the Orders layout and the Orders:_FKCampaignID is changed to 'CAMP00020', how do I get the Order_Line_Items::_FKCampaignID for order 'ORD2010126 to update for all the relevant records in Table Orders_Line_Items?

I tried an 'UPDATE' script with Set Field, but I guess I don't understand it well enough to make it work.

I've attached an image of my relationships.

Edited August 19, 2010 by Guest

[comment]

Why do you need the _FKCampaignID field in Order_Line_Items - and why is it a matchfield in the Orders - Order_Line_Items relationship? Ideally, LineItems should be related to Orders by OrderID alone.

[Spac3man]

Why do you need the _FKCampaignID field in Order_Line_Items - and why is it a matchfield in the Orders - Order_Line_Items relationship? Ideally, LineItems should be related to Orders by OrderID alone.

Because a product is not only purchased based on an order but also on a Sales Campaign. Specifics of a product can change from Campaign to Campaign. Is this a 'no-no'?

[comment]

I am not sure I understand what you mean. What I see from your RG is that a product IS purchased by an order, and that the line item inherits the CampaignID from the parent order. The question is why does this need to be stored in the line item, instead of referenced dynamically from the campaign (through the order).

[Spac3man]

'Widget01' is sold for $9.95 during Campaign 22. During Campaign 24, 'Widget01' is now sold for $11.45. If I don't record the Campaign with each Line_Item, how can I differentiate the Campaigns?

Keep in mind I'm a newb and will do what u ask!

[comment]

'Widget01' is sold for $9.95 during Campaign 22. During Campaign 24, 'Widget01' is now sold for $11.45.

Where is this information recorded? And what should happen to items already sold when you change the campaign of an existing order?

Keep in mind I'm a newb and will do what u ask!

If it doesn't work, you'll get your money back...

Seriously, you should do what you think is best - after understanding the advice you are given.

[Spac3man]

So, if I understand...

Orders have _fkCampaignID AND Order_Line_Items have _fkOrderID so each Widget sold thru relationship has a Campaign associated with it.

Then, if I need to edit the order, the Campaign is changed only in the Order table.

[comment]

Ideally, yes - but there are exceptions. I am trying to determine if your case is such an exception, but you are not answering my questions.

[Spac3man]

Where is this information recorded?

Sorry, I thought this was rhetorical...

the info, should be recorded in Order_Line_Items.

And what should happen to items already sold when you change the campaign of an existing order?

Items should become associated with the new campaign.

[comment]

I am afraid that doesn't answer either one. Of course the price needs to be recorded in line items - but where is it coming from? If there were no campaigns, the line item price would have been looked up from the Products table. But you say a product has a campaign price - so where are these prices?

Re the second question: suppose you have an order for 3 Widgets 01. The order is related to Campaign 22, so the price is $9.95. Now you want to change the order's campaign to Campaign 24 - so the price should really become $11.45. But do you really want to change the prices on an existing order?

[Spac3man]

I am afraid that doesn't answer either one. Of course the price needs to be recorded in line items - but where is it coming from? If there were no campaigns, the line item price would have been looked up from the Products table. But you say a product has a campaign price - so where are these prices?

The price is input by the user at the time of order entry. I know it could come from the 'products' table but the prices change on every campaign. So instead of changing the price in the products table for every Campaign, I chose to allow the user to input the price

Re the second question: suppose you have an order for 3 Widgets 01. The order is related to Campaign 22, so the price is $9.95. Now you want to change the order's campaign to Campaign 24 - so the price should really become $11.45. But do you really want to change the prices on an existing order?

In the end, the ability to change Campaigns on an order is mostly for entry error possibilities. Yes, the prices of products of an existing Customer order can change right up to the time of delivery based on discounts given by the supplying company.

[comment]

OK, so if the choice of the campaign does not influence the price, then what does a line item care about the CampaignID? It's enough that it "knows" its parent OrderID - and the Campaign ID can be fetched from there, if required.

[Spac3man]

so in the end, I took out the second relationship between the 2 tables and everything still works find, in fact better as I can now change the Campaign in orders without affecting anything else.