Using only part of imported text

[LasseJ]

Dear friends,

I am importing data from Excel, where I only need part of the data to make it match a look up. Since I am a newbie it is bit hard to explain, but here is an example:

I have data saying;

'Yes, I hereby confirm that I will pay 9.000 $'

'Yes, I hereby confirm that I will pay 7.000 $'

'No, I will not pay 7.000 $'

'No, I will not pay 12.000 $'

My problem I need it to be either 'Yes' or 'No' to match a look up later in the script.

Can anybody help?

[bcooney]

Is the imported data truly that consistent?

If so, you can import that data into a text field, and create a calc field, flag_Pay = if (leftwords (textfield, 1); 1; "").

I usually don't do Yes/No, but rather just Yes, and yes = 1 in a flag field.

[comment]

I need it to be either 'Yes' or 'No' to match a look up later in the script.

You could set the target field to auto-enter a calculated value (replacing existing value) =

LeftWords ( Self ; 1 )

and perform auto-enter options during the import.

if (leftwords (textfield, 1); 1; "")

That would have worked in version 6, I believe. Nowadays, you'd need =

LeftWords ( Textfield ; 1 ) = "Yes" 

to convert the imported values to Boolean.

[bcooney]

I'd rather not convert imported values. I'd rather reference them in a new calc field (or auto-calc field).

[LasseJ]

Dear all

Thank you for your help. I tried with your suggestions, but my problem is that the Replace field content" function autoenters on both kind of records regardless if LeftWords ( Textfield ; 1 ) turns out 'Yes' or 'No'

I am probably doing something wrong in my script since 'replace field content' is made for all records at the same time and not individually.

Any suggestions?

[comment]

Replace field content" function autoenters on both kind of records regardless if LeftWords ( Textfield ; 1 ) turns out 'Yes' or 'No'

It should enter the first word of Textfield - that is either "Yes" or "No", depending on the original content.

[LasseJ]

i still cant make it work.

Here is my script, maybe it is easier for you guys to see where the mistake is this way

If [LeftWords ( Frivilligkartotek::Ophold med eller uden fly ; 1 ) = "Yes"]

Replace field Contents [ Frivilligkartotek::Ophold med eller uden fly ; 1 ) = "Yes"]

Else If [LeftWords ( Frivilligkartotek::Ophold med eller uden fly ; 1 ) = "No"]

Replace field Contents [ Frivilligkartotek::Ophold med eller uden fly ; 1 ) = "No"]

End if

Hopefully this will help you help me :)

[comment]

The mistake is that if the current record's field starts with "Yes", you replace all records with "Yes". At least that's what I think you do - that's not a real script, is it?

You need to make the calculation for each record, as you replace the contents:

Replace Field Contents [ Frivilligkartotek::Ophold med eller uden fly ; LeftWords ( Frivilligkartotek::Ophold med eller uden fly ; 1 ) ]

[LasseJ]

Jezz...! Have done it all wrong.

Thank you (once again), Comment. !

It works fine now