looping script to join tables

[Alan M]

Greetings all! This forum is getting huge, so I hope I'm posting this in the appropriate place. :

I am trying to create a purchase order with multiple line items from work orders that each have multiple line items and at the end have a relationship between the two so that if the purchase order needs to be changed or canceled it would be reflected in the work order line items.

Let's say there are several work orders and two call for the purchase of routered pieces to be outsourced. I would check a box on both that signifies they are to be collected for the same purchase order.

What I have so far is a looping script that starts in the purchase order table and goes to the work order line items table and then determines whether or not it has a check box that is checked. If it does then it sets the script parameter (which is the purchase order number) and then goes to yet another table (the purchase order line items table) and sets the script parameter there enabling me to set the other related information into the purchase order line items such as the work order number and the particulare work order line item number etc. That creates the relationship just fine and the check is removed from the check box before the loop starts over. The problem I'm having is that on the next check box it finds, the script parameter is set but when it goes into the purchase order line items, the information from the first work order is set into the purchase order line items.

How can stop this from happening? Here is the script that I've made.

Sorry for being so long winded. I'm very new at this and need advice from the pros :)

[Slobey]

not knowing where the script is triggered or how your relationships are set up, I am only speculating here. A script parameter will not change as you go through the records. You are setting fields based on a relationship and it seems the relationship is based on the script parameter, therefore the relationship will be the same and the fields will be set based on that same relationship. If you could attach a stripped down version of the file, it would be easier to ascertain. But if this is the case, you may consider using script variables instead of the script parameter, that way you can reset it. Hope this helps some

[Søren Dyhr]

I have to ask if it's wise to have two identically tables, where you carbon copy the entire set of info if a certain condition is met. It is in my humble opinion better accomplished by letting your checkbox establish the foreign key for the other relation, leaving the lineitem in one place without the need to syncronize between two tables.

The way I think it should work is that purchase and workorder shares the record between them, in a many2many relation.

--sd

[Ender]

Like Soren, I am usually not a fan of using similar tables to hold duplicate data. But I wonder in this case if there could be a good reason. I can imagine an environment where it may be important to keep Work Order and Purchase Order data separate, like if the POs get modified after being generated, but the original Work Order must remain as it was. In this case, it might be relationally cleaner to use separate tables.

But if the POs are essentially the same as the Work Order, just at a different stage of the process, then I'd agree with Soren's recommendation.

[Søren Dyhr]

I can imagine an environment where it may be important to keep Work Order and Purchase Order data separate

So could I (historical data), but the process copying of each field minutiosly, are a little overkill. When you can duplicate the entire record and give it a new record ID to which primary key it should lock. It's just a matter of make the two browsing tables have distinctive ID'ing systems, post or prefixed differently so they never mix.

Take a look at the attachment!

--sd

Dupe2or_not.zip

[comment]

I agree. Circumstances permitting, it could also be implemented in a much simpler way.

DraftParent.fp7.zip

[Søren Dyhr]

Indeed, but how would it rhyme with this:

I would check a box on both that signifies they are to be collected for the same purchase order.

As well as the use of "join" in the threads title???

--sd

[comment]

It's only a sketch of the general principle - I didn't mean to address any specific problem. I did not even address the issue of deleting draft items from the final (and ONLY from the final).

I think both could be solved by adding a conditional key to the item, and using that to relate to the final parent (along with a separate FinalChild TO for this purpose).

[Alan M]

I guess it was a case of "can't see the forest for the trees". The comments posted didn't make much sense to me until I started making a scaled down version to post. It was then that it dawned on me that there is really no need to have the work order related to the purchase order because in the real world if a purchase order changed then you'd simply cancel that one and create a new one....duh. So I went back to relating them using the check box and now everything works as I wanted it to.

Thanks for the posts, they really did make a lot of sense and were very helpful.

[Razumovsky]

Just to throw in my $.02:

Like Soren, I am usually not a fan of using similar tables to hold duplicate data.

This is in certain ways not actually duplicate data. The work order item represents an item ordered from the company by a client, the purchase order item represents an item ordered from a vendor by the company. While often they describe the same physical object, they can split ways as well.

I found the 2 table method much smoother for a company that was constantly re-prioritizing inventory, and swapping clients for items on purchase orders. I think this was one of those situations that Ender imagined.

-Raz