split a field

[Ed Barley]

Hello Everyone,

I Have a field, that needs to be split. I do not know what function the genie uses to split, fields. Have looked in the reference manuals, but do not see what I am looking for.

Have one field with a ship name, and voyage number combined. Would like to strip the voyage number to another field.

"Tamesis V- 3874"

Need to get "Tamesis" the vessel name back in the same vessel field. Get rid of "V-". Get the Voyage number "3874" into the voy field.

I see in the genie how to split with the - . But If possible, during the operation would like to get rid of both the V and -. Or have the V transfered to the Voy field, and Just leave the Vessel name in the origional field. Tried to see what function name is used for an operation like this, but could not find the correct one.

Thanks Ed

[JerryBrightbill]

RE: split a field

Ed

You can use the word() function

word("Tamesis V- 3874",1) = "Tamesis"

word("Tamesis V- 3874",-1) = "3874"

Jerry

[forskare]

RE: split a field

Ed,

1. Go to the Operations tab;
2. New Operation;
3. Update Records;
4. Choose the table which contain the records to be updated; Press the Create Using Genie button;
5. Select Break a Field Into Its Parts, next to the bottom;
6. Follow the yellow brick road.

[Ed Barley]

RE: split a field

Hi Ken & Jerry,

I was going to use the genie, but could not figure out how to get rid of the V from the field.

If I use Jerry's suggestion, does the vessel and voyage need to be in each field before the operation is performed?
So that it leaves the vessel in name in the origional field, and then the voy number in the second field??

Thanks Ed

[forskare]

RE: split a field

My guess is, Yes, it will. Make a backup and give it a try. Worst ya gotta do is delete the T&E version and rename the backup.

kenn

[JerryBrightbill]

RE: split a field

Ed

If you are using an update operation, the first field can be replaced with an expression of itself and the other field can use the other expression. For instance, if the field with the current info is called "vessel" and the new field to fill is "voyage", the expression for the "vessel" field is

word(vessel,1)

and the expression for the voyage field is

word(vessel,-1)

Jerry

[Ed Barley]

RE: split a field

Hi Jerry,

My main problem, as I see it, is that my voy field is only 10 char long. My Vessel Field is 30 char long. So I could not copy the contents of vessel into voy, and use WORD.

What I tried to do, was use the genie, and split my origional vessel field at the "-". And put the voyage number where it was supposed to go. And use WORD to get rid of the V left in the origional field(after I looked up WORD in the ref manual).

What I found was, the split function in the genie does not work.

I wonder if the split function in operations has a problem with the new build. I have not used it(split) for a couple of months, and a few builds ago. When I split the field, the split after - just disappears, and does not go into the field that it was selected to go into. Tried it 5 times(with sample data), to make sure I did not fall asleep during the operation. It does not work. Rebooted the computer, and of course restarted A5. Still no go.

So do not know where to go from here??

Ed

[JerryBrightbill]

RE: split a field

Ed

I think my explanation was not explicit enough. Attached is a screenshot of how an update operation if filled out and uses the expressions. The fact that the voyage is only 10 characters is not a problem because the update just puts the result of the expression in the field. You can update both fields at the same time in one update operation. The genie is helpful, but some operations are easier to do with a simple update. I used the genie, but just went to the general tab and selected Assign a calculated value to a field.

Jerry

[Ed Barley]

RE: split a field

Well I lied a little(or did not give you enough information like I should have). So I do not drive you nice people nuts.

The vessel name can have up to 3 parts like:

"SAGA BEIJA FLOR V-45TY", so that complicated it a little.

After looking in the manuals, and looking up word, I see that it needs to be exact. Will have to pick up in the morning.

Thanks Ed

[Keith Hubert]

RE: split a field

Hi Ed.

Are all fields with the Vessel and Voyage exactly the same format ie. "SAGA BEIJA FLOR V-45TY" Vessel name followed by V-????. If so then you use the AT() function on the "-" that way you acheive just what you want and lose the V. Have a look on the board here I did show someone else how to it.

Keith Hubert
London.

[Keith Hubert]

RE: split a field

Ed,

Check out thread Msg 20083.

Keith Hubert
London.

[JerryBrightbill]

RE: split a field

Ed

Don't worry about driving us nuts. Many of us are experts on goofy behavour. Keith is correct, if you always have the "V-" for vessel, you can use the at() function in combination with the substr() function to get the text string before the V

substr(vessel,1,at("V-",vessel)-1)

You may want to use alltrim to remove leading and trailing spaces. This will give the text string after the "V-".

substr(vessel,at("V-",vessel)+2)

Jerry

[Ed Barley]

RE: split a field

Just waiking up at work on the West Coast

So yes, the vessel name is first, and then a "V-" was(is) between the vessel name and voyage number.

As soon as I get caught up, I will let you know my success.

Thanks for the attentiion, and help.......... Ed

[Ed Barley]

RE: split a field

How pickey, can I get???

Used..... substr(vessel,1,at("V-",vessel)-1)........worked perfect, for the vessel name.


Ended up using....substr(vessel,at("V-",vessel)+1).....it split the voyage number with the - in front of it. How pickey can I get to get rid of the - in front of the voyage number?? Or can the - be stripped from the voyage field with another function?? I've tried to play with what you have given me, but can't seem to get the - to disappear, on this side of the -.

Thanks Ed

[Robert Adler]

RE: split a field

Ed:
make it +2 to drop the "-"

I hope this helps

bob adler