How to extract data to fields

[cos]

Hi. What script steps would extract the data from one text field and place them into separate fields.

The field text is this:

[{"Ship To":"Company | Full Name | Address | City | State | Zip"}]

 

I need to set field data into corresponding fields: Company, Full Name etc

I have control of how the data in the text field is formatted. I used vertical bars, but can change to something else.

 

Thanks

 

[comment]

Is this the exact content of your field:

[{"Ship To":"Company | Full Name | Address | City | State | Zip"}]

including the square and curly brackets and the quotes? And what is the meaning of the "Ship To":" part? Is it a constant that appears in every record, or does it change?

[cos]

Yes. Everything in that line is in the field including curly brackets and quotes. The "Ship To" is the label. I am able to change that to "" so I just placed that text.

Constant in every record. Never varies.

The "Ship To" label is to be ignored. Don't need that at all. I can also remove the space before and after the vertical bar or change the bar to a dash. It needs to be fairly easy to read on the front end.

[comment]

Okay, then try telling your script to set a variable to =

Let (
t = Middle ( YourTable::YourField ; 13 ; Length ( YourTable::YourField ) - 15 )  
;
Substitute ( t ; " | " ; ¶ )
)

and then do a series of Set Field[] steps:

Set Field [ YourTable::Company ; GetValue ( $variable ; 1 ) ]
Set Field [ YourTable::FullName ; GetValue ( $variable ; 2 ) ]
Set Field [ YourTable::Address ; GetValue ( $variable ; 3 ) ]
...
Set Field [ YourTable::Zip ; GetValue ( $variable ; 6 ) ]

Note that this assumes your field does not contain any carriage returns as part of the data.

[cos]

Thanks for this. I understand the Set Fields. I'm a bit confused with the variable. After studying your solution, I think for my understanding, it might be better to replace " | " with simply "|" in the source text. So an actual real world text string would look like this:

[{"":"The Test Company|John Doe|1234 Main Street|Beverly Hills|CA|91210"}]

In this example "Ship To" is replaced with ""

Seems that the source always sends "" for a non-entry in that part.

Looking forward to your solution.

[comment]

6 minutes ago, cos said:

I think for my understanding, it might be better to replace " | " with simply "|" in the source text.

It doesn't really matter what you use as the delimiter, as long as it doesn't appear as part of the actual data. But of course, if you use "|" then you must also replace:

Substitute ( t ; " | " ; ¶ )

with:

Substitute ( t ; "|" ; ¶ )

 

And if your string does not start with:

[{"Ship To":"

you must also adjust the parameters of the Middle() function, whose role is to strip the prefix an suffix and leave only the actual fields, separated by the delimiter. I don't know how much control you have over the string format, but ideally it would be only this:

The Test Company|John Doe|1234 Main Street|Beverly Hills|CA|91210

If you cannot avoid the prefix and the suffix at the source, then you must start by removing them. With your "new" format, you would that by defining t as =

t = Middle ( YourTable::YourField ; 7 ; Length ( YourTable::YourField ) - 10 )  

 

[cos]

Okay. I get it. Done. Works perfectly. You are the champ.

[cos]

And now for part 2 and final of this extraction. We have another text field that is very similar, but has some distinct differences. The text contains a series of data to be placed in Line Items.

 

Each Line item looks like this: Item_ID  Description  Quantity

 

The imported data always looks like the following example for 3 line items, but there could be many more. Commas separate each set of line items. There will never be a comma elsewhere.

 

[{"":"2","unique:2[]":"Machines | 00254 | 2.75’’x5.25’’ (1390-LAB 3) vinyl laminated 290 4 to a sheet"},{"":"34","unique:2[]":"Misc | 00249 | 2.75’’x1.236’’  (Proudly Built) vinyl laminated 290"},{"":"10","unique:2[]":"Redbox | 00266 | 7.9\"x 3.9\" (RBL_outline) vinyl laminated 290"}]

 

There first "2" is the Quantity.

"00254" is the Item_ID

 

Quantity and Item_ID are all we need to extract for each line item because the description is already in Filemaker with the Item_ID

 

I appreciate any commentary.

[comment]

40 minutes ago, cos said:

Commas separate each set of line items. There will never be a comma elsewhere.

I am afraid you are wrong about that. If this were true, then there would be only 2 commas in the entire example - but there are in fact 5 of them.

AFAICT, the items are separated by "},{" - so if you substitute that with a carriage return, you will have a list of items you can loop over.

To extract the ID and Quantity from an individual item, we need some rules in addition to the example. At least I don't detect any consistent structure one you could use to separate the "fields".

[cos]

Ah. You are right about the commas. I got mixed up with the previous extraction being one field. The Quantity is a separate field from the rest of the infos in the source and it is joined in the export. Yes, "},{" would give us a carriage return to loop.

I can restructure the data a bit that may give a consistent structure by placing Item_ID at the start like this:

[{"":"2","unique:2[]":"00254 | Machines | 2.75’’x5.25’’ (1390-LAB 3) vinyl laminated 290 4 to a sheet"},{"":"34","unique:2[]":"00249 | Misc | 2.75’’x1.236’’  (Proudly Built) vinyl laminated 290"},{"":"10","unique:2[]":"00266 | Redbox | 7.9\"x 3.9\" (RBL_outline) vinyl laminated 290"}]

Which would always render exactly the same except qty would be 1-4 digits

{"":"2","unique:2[]":"00254

{"":"34","unique:2[]":"00249

{"":"10","unique:2[]":"00266

 

 

[comment]

As I said, we need some rules here. If you can say that the quantity will always be between the 3rd and the 4th quote of an item, then you can extract it as =
 

Let ( [
start = Position ( $item ; "\"" ; 1 ; 3 ) + 1 ;
end = Position ( $item ; "\"" ; 1 ; 4 )
] ;
Middle ( $item ; start ; end - start )
)

Similarly, if the ID is always between the 7th quote and the following vertical line (surrounded by spaces), then it can be retrieved using =

Let ( [
start = Position ( $item ; "\"" ; 1 ; 7 ) + 1 ;
end = Position ( $item ; " | " ; start ; 1 )
] ;
Middle ( $item ; start ; end - start )
)

 

Edited June 13, 2016 by comment

[cos]

All week I have read up on how to set the variable and set the fields in a loop and cannot figure this out. This is my first try at the Let function. It's pretty versatile. It's hard to find examples of how to use it all together in a script. Could you give an example of Set Variable and Set Field using Loop and your rules?

[comment]

See if the attached demo helps:

 

GenerateItems.fp7

[Trente]

Hi. I have a similar problem. Have tried to modify the attached demon, but I can not get it to work.

I want to extract "Quantity" which is always between the 1st and 2nd "|" and "id" which is always between the 4th and 5th "|".

The text field is this:

Item_name1|Quantity1|Price1|Tax1|Id1|Item_name2|Quantity2|Price2|Tax2|Id2|Item_name3|Quantity3|Price3|Tax3|Id3|

Thanks in advance!

[LaRetta]

Hi Trente,

To get Item_name1 ... GetValue ( Substitute ( textField ; "|" ; ¶ ) ; 1 )

To get Quantity1 ... GetValue ( Substitute ( textField ; "|" ; ¶ ) ; 2 )

... and so on, where textField is the name of your field holding this string.

So the number at the end is the 'location' of the value you wish to extract.

[comment]

 

Your question can be read in several ways. To me, it seems like you have a field that holds data that needs to be split into multiple records (three records in this example, possibly any number of records?), with each record extracting its own Quantity an ID values.

Also, there seems to be no dedicated record separator, so one must know that every 5 items form a separate record.

Is this correct? if not, please clarify. If yes, see if the attached works for you.

 

 

 

 

 

GenerateItems2.fp7

Edited August 14, 2016 by comment

[Trente]

Absolutely spiffing! 
The demo you attached works perfectly!
Many thanks Comment! And thanks for answering LaRetta, but the the gold medal goes to Comment this time...

[comment]

Actually, LaRetta deserves a lot of credit for for this, as she caught two errors of mine and was kind enough to notify me back channel.

[LaRetta]

Hey Trente, I never mind taking backseat - what matters is you get the best answers we can give!  Remember to give a post a LIKE rating when someone has taken their time to provide you with an awesome response such as the one you just received from Comment.  

[Trente]

Hi LaRetta and Comment!

Many thanks to you both! I am very grateful for the solution you two put together. This was my first record (but definitely not the last ...) and I never thought that my problem would be solved so quickly!

[Trente]

...this was my first POST... Sorry about my bad english!