Hello.
I have a master order table and a secondary table called “products”. My form does a auto lookup on the products and stores its product name and price in the master table.
Problem is when the a product and its price is updated it reflects the change in the master table as they are linked.
I want to keep a history of orders where the product and price do not change.
How can I have this and still have the auto lookup of the Product and its Price?
Thanks for any help.
[QUOTE=instore;27157]Hello.
I have a master order table and a secondary table called “products”. My form does a auto lookup on the products and stores its product name and price in the master table.
Problem is when the a product and its price is updated it reflects the change in the master table as they are linked.
I want to keep a history of orders where the product and price do not change.
How can I have this and still have the auto lookup of the Product and its Price?
Thanks for any help.[/QUOTE]
in general you would create a table order and a table orderline. The table orderline contains the (copied) productid, desc, price and tax.
I’m a little confused?
So my field that does the auto lookup for the products is linked to the product table. the product_id is inserted into my master orders table which is fine but right now if a product is updated it reflects its change in the master orders table.
Your saying I need a middle ground table?
If you could explain/example in more detail I would appreciate it.
Hi Aducom.
Sorry to be a pain but could you explain exactly how to accomplish what your suggesting?
Thanks very much.
Albert, let me help here.
I want you to think of it this way,
Since you have an orders table, you also need an orders line table.
When you save the order line you should populate the orders line table with the unique values at the time of saving the line.
You have to remember that price changes need to be saved at the time of inserting.
So, when you do your selection, you should set the quantity, unit price and any other pertinent info into variables and
Then insert the values into on the after insert event.
Hope this helps
Kevin
Ok…Maybe these pictures will help simplify what I’m trying to do.
In Picture-1 You can see the Sales Person (TEST).
Now if I change the sales person name (Picture-2) to “TEST3” then that change updates the master table as seen in (Picture-3).
I want to keep a history of each order without that change happening on the master table.
I must have something setup wrong?
It looks like you are changing a sales person’s name in your sales person’s table. And that is quite a valid thing to do in some cases, e.g. a person’s name changes because they got married. So, the record detailing the person (ID key, address etc) stays the same but their name changes. So then the behaviour you describe is what you want. That is, that person was still the one who sold that order, just their name has changed - and that’s all.
However, it sounds like what you actually want is to do is change the actual sales person who did the order, i.e. it was originally assigned to the wrong sales person perhaps? In which case pick (in your pic1) a different sales person from the drop down. If they are a new person (and do not in the list) then create a new sales person record for them first - so you can choose them from that drop down. What you don’t want to do is change the name of the sales person to a different name in their sales person record. There should be only one record for every sales person. As new people arrive they get added as new record - as people leave they should NOT be deleted if you want your historical sales order information to stay accurate. Just because someone has now left does not mean that the orders they did were not sold by them, right?
So if I have got your meaning, it looks like you are overwriting names. The question is why? If the reason is so your drop down is not a huge list then you simply need to add a new yes / no (or similar) field to the sales person table and call it Active to show who are active sales people - tick thos in there that are active and blank out those are not. Then change the lookup SQL behind the drop-down to add a WHERE clause for showing only those that are active.
I agree, I am unsure of the rational of changing the salesperson
You could always create another column called created by and make it the original salesperson and make it readonly so it can’t be updated
Again
Unsure what you are trying to accomplish
Kevin
Thanks adz1111.
Your insight is correct for the most part. I tried your suggestion but here’s the problem.
I have added the column “active” to the sales person table and sales people are marked active or not.
In the orders table I changed the sql lookup of the sales person to the where clause (WHERE active = “1”) where “1” is a active sales person.
This works for the drop down where it only shows active sales people from the sales person table but…
When the Sales Person value is set to active (Lookup-4.jpg) the orders table Sales Person field is happy and displays the Sales Person “TEST” (Lookup-5.jpg).
Now when I go back and set the Sales Person “TEST” to NOT active “0” (Lookup-6.jpg) the orders table sales person field displays only the Select title as it doesn’t find that sales person (“TEST”) in the table anymore as its marked not active. (Lookup-7.jpg).
How do I get this to work using your work flow of sales people being active or not but still showing up in my orders table?
Thanks for your help!
Hi instore
As far as I can see it is working correctly now. All 4 pictures behave as expected.
If a sales person is no longer active why would you want it to appear in that drop down? As far as I can see, that drop down is for assigning sales people to the appropriate order (as opposed for searching for all orders by a particular salesperson). If a sales person is no longer active then you shouldn’t be “allowed” to assign them. Of course, if it is realised a mistake was made and you do need to assign an old inactive person to an old order, then you would make them active temporarily, assign them, and then make them inactive.
Now, if your point is that you wish to see all orders by all sales people - active or not (or search for all orders by a particular sales person - active or not), then you don’t use that dropdown, as it is tied to the salesperson field in the order. You need a separate form (or possibly just a separate “search” dropdown on the top of the same form) that uses a slightly different query to the one we just did - basically it is as you had it originally, i.e. remove the where clause for “active”.
Hi, just let me jump in.
I think what instore probably wants (correct me if I’m wrong) is to be able to choose only active persons for new orders but see all persons on existing orders.
To do so, create a global variable for your order-id (if you haven’t already) and set it to ‘Out’.
onScriptInit:
[glo_order_id] = 0;
onLoad:
[glo_order_id] = {your_order_id_field};
if(sc_btn_new)
{
[glo_order_id] = 0;
}
Change the WHERE clause of the SQL statement of the select field.
WHERE IF([glo_order_id] > 0,active IS NOT NULL, active = 1)
This should give you the effect you want.
However, you have to take care of orders not getting accidentally overwritten (onChange event, set the field readonly, etc.).
Hope this helps.
jsb
jsbinca
Ah that makes more sense.
I agree with only active people on NEW orders, although I would say to see ONLY active people on OLD orders, plus (only when a specific order has an old person), that old person?
Hey jsbinca,
That’s pretty close to what I wanted and kinda cool!
What I really wanted was the “Sales Person” select field to show what’s stored in the table regardless if the sales person is currently active, but was active at time of insertion and then when you click on the drop down it only shows active sales people to choose from if you want to replace that sales person.
Not sure if that’s doable but that’s what I was looking for.
PS. You guys are awesome for helping out!
instore,
Well you could do that - but you would need another 2 fields in the sales person table. Date_Activated and Date_Deactivated. They could be auto-populated with an onchange event check at “addition” / “update” time.
Armed with that information, you could change your dropdown’s SELECT to show all sales people who are currently active, AND any who are deactivated now, but were active on that order date?
Thanks adz111.
So what would the drop down’s select statement look like to accomplish that?
Something like:
SELECT
tblsalesperson.name
FROM
tblsalesperson
WHERE
active = 1
OR
(active = 0 AND '{orderdate}' BETWEEN act_date AND deact_date)
ORDER BY
name ASC
{orderdate} is the order date from the form’s current sales order