Basic Update Records question

[woodb]

I use Update Records command regularly. It seems very simple. But, when I want to update records and replace the value in a field with the value of another field, I've been using the TRANSLATE function to get the field information in the "with" field of the form:

When the following formula is true, replace values in Commodity with TRANSLATE(Commodity2,"","")

This seems to work, but I'm wondering if this is the proper method.

Thanks,
Brian

[Ken]

Hi Brian,

I see that you've been following the online help example very closely. We'll have to add another section in the middle that is slightly less advanced -- that example was meant to show how more complicated formulas can be used in "with" area of a replace operation.

For your situation, you just need to add the field name, since that is technically a "formula" too:

Code: Select all

Replace:  field1
With: field2
When:  field1 = "test"

So, your three parts would look something like:

Code: Select all

Replace:  Commodity
With:  Commodity2
When:  contains(commodity,"widget")

Note that if you wanted to actually replace the value with the word "Commodity2", you would just need to put quotes around it to tell Strata that it's not a field name.

Regarding the translate function, this is really useful for things like data cleanup. In the update example in the help manual, it was purely used to replace periods with nothing (basically, deleting periods from a field). So, if I had a mixed address field with P.O. 123 and PO 456, the translate was a way for me to normalize all my P.O. Box addresses to the format "PO" -- sort of a poor man's regular expression.

What you were doing in the update operation was replacing with a field that was modified to translate nothing to nothing -- so the formula did work correctly, if not a bit circuitously.

Hope that helps,
ken

[woodb]

Hi Ken,

Thanks for the info. Believe it or not I use update a lot. I think that was my problem. 99% of the time I want to just fill in the value with just text. On the rare occasion that I wanted to put in the contents of a field, I thought I needed to do something different. Since my field was a real word (Commodity) I was sure if I put Commodity in the form that's what would get updated everywhere. I didn't realize Strata would first look to see if that was a field name and put in it's values if it was and if it wasn't just put in the word Commodity.

Thanks again,
Brian

[Ken]

Hi Brian,

You rationale is completely logical, particularly coming from text-only usage. We'll need to add something into the next version of our help manual that talks about how field names trump plain text in this situation...

For posterity, should someone else happen upon this post, here are the rules for "replace with" (assume we have one field in our table called: myfield , with a value of: ABC ):

1. Replace with text (1):

Code: Select all

"mytext"

will appear in your cell as:

Code: Select all

mytext

2. Replace with text (2):

Code: Select all

mytext

will appear in your cell as:

Code: Select all

mytext

3. Replace with text (3):

Code: Select all

'"mytext"'

will appears in your cell as:

Code: Select all

"mytext"

4. Replace with text (4):

Code: Select all

"myfield"

will appears in your cell as:

Code: Select all

myfield

5. Replace with the contents of another field:

Code: Select all

myfield

will appear as:

Code: Select all

ABC

6. Replace the contents with a formula:

Code: Select all

contains(myfield,"my")

will appear as:

Code: Select all

ABC

Thanks for the post,
ken