Multiple Updates to the Same Field

[cgbjr]

I can write an Operation that updates a Field. I need to update this one field to about 12 different values based on other fields in the db. Is there a way to write all these updates into one (1) Operation? Or is there a way to run 12 operations with one command? I have tried updated the same field in two different ways in a single Operation but only the first update works - the second is ignored.

Thanks - Chuck B

[Tom Cone Jr]

Re: Multiple Updates to the Same Field

The field value to be written to the field can be defined using an expression. The CASE() function might be a way to arrange for the expression to return 12 different values based on 12 different conditions. This would result in a single saved update operation and eliminate the need to run multiple update operations.

[Stan Mathews]

Re: Multiple Updates to the Same Field

I have tried updated the same field in two different ways in a single Operation but only the first update works - the second is ignored.

I would expect that if you examine the results, both updates worked but the second overwrote the first. When you define an update operation, you can specify

the field to be updated
the expression to use for the update
the condition under which to perform the update

You could have

field---------expression-----condition
fielda--------upper(fieldb)---left(fieldc,1) = "B"
fielda--------upper(fieldd)---left(fieldc,1) = "D"
fielda--------upper(fielde)---left(fieldc,1) = "E"
fielda--------upper(fieldf)---left(fieldc,1) = "F"
'many more combinations
fielda--------upper(fieldz)---left(fieldc,1) = "Z"

If you don't specify conditions, as indicated, the fielda above would first be updated with upper(fieldb), then upper(fieldc), etc with the result in fielda of upper(fieldz)

[G Gabriel]

Re: Multiple Updates to the Same Field

I need to update this one field to about 12 different values based on other fields in the db

I am not sure what this means?!
In the end, the field will have ONE and ONLY ONE value, the last value used for updating that field. What's keeping you from getting there the first time around?
You need to explain this a little bit better.

[cgbjr]

Re: Multiple Updates to the Same Field

G Gabriel - I'm doing a conversion from an old DOS db to Alpha. I'm creating two new fields based on one old field that has about 20 different values spread over 10,000 records. So, I need to run a series of expressions and hoped to do it with one operation that, line by line, assigned about a dozen new values.

Stan - I went back and tried again and I guess I had some bad logic in the original second line, because it works now. I don't think one expression was overwriting another, just that one expression wasn't working at all. There was no error message, but I still think there was an error. So, I am able to run a series of changes to the same field in one operation. Huzzah!

[john linley]

Re: Multiple Updates to the Same Field

QUESTION RELATING TO UPDATING FIELD VALUES?

Assume I have a table named "DY_STYLES"

It is linked by "style number" to another table called "BOM"

The BOM table contains components or parts that make up various styles. A component can be, and often is, in many different styles.

In this BOM a particular component is named "2662p-ss" and has the following fields which need to be changed.


"source" char 4
"gram_wgt" num 7 4
"cost" num 6 2


On occasion, any one or all of these fields, may need to be changed.

A typical change might be: source from city to onty
gram wgt " 3.450 to 3.453
cost " 4.50 to 5.50

"City" may be the original or current supplier. "Onty" may make the piece also but gives quicker delivery, so we want to switch sources. However, Onty's casting, in this case,
is slightly different in gram weight and cost.

Since this component exists in thirty or forty different styles. we need to change
gram weight and cost also for every place this component exists in the BOM.

A procedural example of how to accomplish this would be much appreciated.

Hope someone notices this and can point me in the right direction..

Thanks in advance,
John Linley

[Stan Mathews]

Re: Multiple Updates to the Same Field

A copule of points that might help. You can only update the parent table of a set if you want to use values from one or more children. You didn't specify which of the tables was the parent.

You can conditionally update selelcted parent records if you can define an expression which returns a logical result. To simplify, you can define an update operation which takes the desired values from DY_STYLES and places them in the desired fields in BOM with the condition ------ component = "2662p-ss". That's not very useful since you would have to change the operation each time you wanted to change another component.

You have to decide how to write the condition expression that works for you.

You might consider having some sort of form based on a set with BOM as the parent and DY_STYLES as the child. The fields from DY_STYLES would be in an embedded browse on the form. Double clicking on a row in the browse could be coded to take the values in that row and place them in the desired fields in the parent table.

[john linley]

Re: Multiple Updates to the Same Field

A copule of points that might help. You can only update the parent table of a set if you want to use values from one or more children. You didn't specify which of the tables was the parent.

You can conditionally update selelcted parent records if you can define an expression which returns a logical result. To simplify, you can define an update operation which takes the desired values from DY_STYLES and places them in the desired fields in BOM with the condition ------ component = "2662p-ss". That's not very useful since you would have to change the operation each time you wanted to change another component.

You have to decide how to write the condition expression that works for you.

You might consider having some sort of form based on a set with BOM as the parent and DY_STYLES as the child. The fields from DY_STYLES would be in an embedded browse on the form. Double clicking on a row in the browse could be coded to take the values in that row and place them in the desired fields in the parent table.

[john linley]

Re: Multiple Updates to the Same Field

Stan: Thanks again for trying to help. Hopefully, this time, I will attempt to provide better information. First: "DY STYLES" is the parent and "BOM" is the child.
The link between them is "STYLE NBR".

A component number such as "2662P-SS" exists only in the BOM, but will exist for each style that uses that component. Some components may exist in as many as 60 different styles.

As stated earlier, for practical reasons, we may need to update a component.

I have been able to global update values in a single field elsewhere using ask variables. I suppose I could do this again.

I have not had, before this, needed or wanted to globally multiple fields simultaneously in the same table.

Since I haven't ventured down this particular road before, I solicited assistance.

I did make a stab at it, by creating a button on a form of the BOM that called up a checklist dialog box, with the 3 fields to be updated on it. At this point I became very uncertain as to executing the next step.

I hope this second explanation of my needs is sufficient and updating three fields in one pass is a possibility. It would certainly be more efficient and look more professional.


Kindest regards,

John

[Mike Wilson]

Re: Multiple Updates to the Same Field

John,
Sounds like a many-to-many setup.

[john linley]

Re: Multiple Updates to the Same Field

MIKE W:

In response to your comment, "dy_styles" is linked to "BOM" as a one to many link.
There are approximately 700 styles in the DY_styles" table , and approximately 3000 records ( or parts) in the BOM table.
The link between the two is the style #, a 26 character field.

Individual components, eg: ."01carat diamond" may exist in three hundred different styles, from every quantity to one to 136 Other components may exist in only a dozen or so styles.

Basically, I asked for help in globally updating more than one field in the table "BOM" for a particular component such as "2662p-ss" in one step instead of multiple steps.

Thanks for responding, and I hope this further explanation satisfactorily explains.

I haven't surrendered either...
Regards, John L.

[Mike Wilson]

Re: Multiple Updates to the Same Field

John,
A Dy_style record is linked to many BOM components. Each BOM component is linked to many dy_styles. This generates a Many-to-many relationship. To view all the BOM records linked to the style, the style table is the parent. To view all the styles linked to a given component, the BOM table is the parent. To view both at the same time, I believe you need an intermediate table. I believe I have an example somewhere I'll try and find.

How do you have yours set up? How you have that set up will make a difference on how you perform this action.

[john linley]

Re: Multiple Updates to the Same Field

TO: MIKE W:
Mike, My set structure, {DY_STYLES to BOM} is defined as a "one-to-many" link.
The linking field is the style number.
No other structure links these two tables together. I am at a loss to understand
how there could be a "many-to-many" link by components! There are no component values in the parent table "dy_styles".

Each style of jewelry has quantities of components ranging from about 4 components to a dozen components consisting of diamonds; metal(castings);, precious and semi-precious stones.

There is a separate table "LABOR: also linked to this set. Items of labor are: setting, jewelry, polishing, etc.
The actual structure of the set "bill of material", is: "dy_styles" linked to table "BOM" one-to-many" and also linked to "labor" one-to-many.


Since the set link, by style number is one to many, where does the other link many to many come from? I have only been in Alpha5 for a little over a year, and am aware of some of the differences to alpha4 but certainly not all.
I also don't understand how this affects wanting to edit field/s in just the table "BOM"!

I sincerely appreciate your interest,

Regards,
John L

[Mike Wilson]

Re: Multiple Updates to the Same Field

John,
I have an example for you. Pardon me that I used DOM instead of BOM (my mistake). I will send it but it will take some explanation that I don't have time right now to generate. I will send the example and you can look at it. The component change button on the form will change the values of a chosen component in the entire DOM table. You will see this in the Upper DOM browse, not the lower. There is a way though proper relational table and set structure to be able to make the change in one component table value and that will relate through the application. That is the second (lower) DOM browse on the form.

I will get back with you later if you need mor explanation.

[john linley]

Re: Multiple Updates to the Same Field

Mike

Received your zip and have copied it to a5 in an adb called m2m.
I have already taken a quick look at the set structure and find it interesting. I will need more time to study this at length.
Thanks very much for the assistance, and I will definitely report back to you sometime this coming week.

My sincerest appreciation,

John L