Extract word from one field to another

[jpayne]

Ok I have searched the forum for this answer - I know its here because there was a thread that got me this far but now I can't find it.

I have a field "keylink" it's the field I created so I can do a ref integrity on the set.

I've got the keylink to work, the field rule combines 3 other fields into one with a plus sign between each field data.

"Jobnumber"+"Lineitem"+"PurchaseOrder"
ex.
07-J115+001+25681178

The purchase order and lineitem characters may vary, they could contain 3 characters or 11 from record to record.

My child record gets the keylink data correctly, now I need it to be separated into its corresponding fields in the child dbf. So Jobnumber part of field goes to the job number field, Lineitem data from the keylink goes into the lineitem field, PurchaseOrder data from the keylink goes into the PurchaseOrder field.

I have used this expression in the field rule for "JobNumber"
?word(keylink,1,+)
If I understand this expression correctly it should work as this - since keylink is the field I'm looking in, and the word I want to extract is the first word (1), and the delimiter is the + I should get just the jobnumber 07-J115 to populate the jobnumber field

Instead I can't even get that far - it's not a good expression. What "),.' am I missing?

Thanks in advance for the help. If need further info let me know.

[Stan Mathews]

Re: Extract word from one field to another

The ? is used in the interactive editor to display the result of the expression, ? is short for print.

word(keylink,1,"+")

should yield the first word in the keylink field.

'this is the interactive editor
''assign a test value to a variable to use in the test expression
keylink = "07-J115+001+25681178"
'now try the variable in an expression to see if it does what we want
? word(keylink,1,"+")
= "07-J115"

? word(keylink,2,"+")
= "001"

? word(keylink,3,"+")
= "25681178"

When you read the help for a function and see something like

Word_Delimiter as C

Read it like

WORD( Input_String as a character string, Word_Number as a number [optionally because everything past a left bracket is optional, Word_Delimiter as a character string [optionally because everything past a left bracket is optional, Word_Count as a number ]] )

and when you read as a character string, this means you can substitute anything that yields a character string.

the name of a character field
the name of a character type variable
an expression that yields a character string
a literal character string
etc.

If you pass a literal character string you must enclose it in quotes to tell Alpha that you are not using one of the other choices.

[Al Buchholz]

Re: Extract word from one field to another

?word(keylink,1,+)

quotes are needed around the plus sign

?word(keylink,1,"+")

ps - and what Stan said...

[Tbaker]

Re: Extract word from one field to another

Just reading what you have, I would say that your field rule for the child does not know where to get keylink - if keylink is a field in the child then
"word(keylink,1,"+") will produce the first word in keylink.


You were showing ?word(keylink,1,+) - I don't know what the question mark is - but then the plus sign should be in quotes.

Hope this works for you

Tom

[G Gabriel]

Re: Extract word from one field to another

I've got the keylink to work, the field rule combines 3 other fields into one with a plus sign between each field data.

"Jobnumber"+"Lineitem"+"PurchaseOrder"
ex.
07-J115+001+25681178

"Jobnumber"+"Lineitem"+"PurchaseOrder" will not produce:

07-J115+001+25681178
but rather:
07-J11500125681178

To get 07-J115+001+25681178 the expression should be:
Jobnumber+"+"+Lineitem+"+"+PurchaseOrder"

and it probably be better to use an odd character like a tilde or a pipe instead of the plus sign.

[Stan Mathews]

Re: Extract word from one field to another

I've got the keylink to work, the field rule combines 3 other fields into one with a plus sign between each field data.

"Jobnumber"+"Lineitem"+"PurchaseOrder"
ex.
07-J115+001+25681178

"Jobnumber"+"Lineitem"+"PurchaseOrder" will not produce:

07-J115+001+25681178
but rather:
07-J11500125681178

To get 07-J115+001+25681178 the expression should be:
Jobnumber+"+"+Lineitem+"+"+PurchaseOrder"

and it probably be better to use an odd character like a tilde or a pipe instead of the plus sign.

No real reason to use word separators since the concatenation can just as easily be parsed with substr()

If a valid keylink value is "07-J11500125681178" then we know that the concatenation of

"Jobnumber"+"Lineitem"+"PurchaseOrder"
is composed as indicated of
07-J115+001+25681178

then we know where we need to break the string.

keylink = "07-J11500125681178"
? substr(keylink,1,7)
= "07-J115"

? substr(keylink,8,3)
= "001"

? substr(keylink,11)
= "25681178"

[G Gabriel]

Re: Extract word from one field to another

then we know where we need to break the string.

No, we don't:

The purchase order and lineitem characters may vary, they could contain 3 characters or 11 from record to record.

[Stan Mathews]

Re: Extract word from one field to another

No, we don't:

Yes, we do..

If the valid, untrimmed, concatenation per the expression given

"Jobnumber"+"Lineitem"+"PurchaseOrder"

yields

"07-J11500125681178"

then the defined field length of "Jobnumber" is 7 since there are no trailing spaces. The defined field length of "Lineitem" is three since there are no trailing spaces. The defined field length of of "PurchaseOrder" is unimportant since we can omit the second parameter of substr() for the last portion and capture the rest.

The purchase order and lineitem characters may vary, they could contain 3 characters or 11 from record to record.

but the concatenation of same would be something like

"07-J11501 256811  "

so they start and end in the same position in the string.

[G Gabriel]

Re: Extract word from one field to another

The defined field length of "Lineitem" is three since there are no trailing spaces.

if you use your logic, then you should have 8 spaces between lineitem and PurchaseOrder since lineitem could be anywhere from 3 to 11 chr, meaning the untrimmed field will have 11 spaces.

That would yield:

"07-J115001        25681178   "

If you use untrimmed fields, then you could use substr()

By the way:
"Jobnumber"+"Lineitem"+"PurchaseOrder" will not even produce:
07-J11500125681178
but actually:
JobnumberLineitemPurchaseOrder !!

[Stan Mathews]

Re: Extract word from one field to another

if you use your logic, then you should have 8 spaces between lineitem and PurchaseOrder since lineitem could be anywhere from 3 to 11 chr, meaning the untrimmed field will have 11 spaces.

That would yield:

"07-J115001        25681178   "

If you use untrimmed fields, then you could use substr()

By the way:
"Jobnumber"+"Lineitem"+"PurchaseOrder" will not even produce:
07-J11500125681178
but actually:
JobnumberLineitemPurchaseOrder !!

I can't imagine what part of the "if" in "If the valid, untrimmed, concatenation per the expression given" eludes you. Please go to my user profile and add me to your ignore list and I'll do the same for you. I've had enough of your arguments.

[G Gabriel]

Re: Extract word from one field to another

First: I didn't start this argument, you have.
Second: I am afraid your argument is flawed.
I am not sure how you square these two statements:

If the valid, [B]untrimmed[/B], concatenation per the expression given

with this:

The defined field length of "Lineitem" is three since there are no trailing spaces.

If it's untrimmed, and if the field allows up to 11 chr, then there should be 8 spaces between Lineitem and PurchaseOrder.

The better "if" to understand here is:
IF you can't stand the heat...don't start an argument.