Extract data from Notes field

[mart10]

I am using crystal 10

I see a Notes field (>500 char in length that contains very stuctured data as below

GroupA: gdgghdgfd,jsshsjd,hffefheiuf;
GroupB:ddgggdgdh,jjsdhdsdsi,uwu,skhwdhdwdwhdw,jhjwhwhwpaqqqi;
BIBID: jhgddggdsgdh;
BBM: jdhjdshjdshjsajklqpoqpw;
etc

It is structured in that the first part contains in effect field name followed by: then data (which may itself be seperated by a , and then finally a ; followed by cariage return to move the inputter to the next category (field) of data. YES i know its a mess but this is off the shelf package and we ran out of fields.

My question is how do I extract the data into a report? The 'field headings' eg GroupA will allways be there as will the : and the ; although may contain no data

 

[fisheromacse]

Since say the data is very structured, i will assume (probably incorrectly) that you will always have not just the same format, but the same number of items you are trying to extract. if so, i would use split and create a formula for each line you needed.


\\{@Split_upper}
IF [Ubound(Split({CM_ISP.DTS},";"))] > 0 then
Split({CM_ISP.DTS},";")[Ubound(Split({CM_ISP.DTS},";"))]
else "Not Found";

\\{@Split_1st}
IF [Ubound(Split({CM_ISP.DTS},";"))] > 1 then
Split({CM_ISP.DTS},";")[1]
else "Not Found";


you can split on any item you need, and can nest splits if needed to get more granular data.

It is a Monday in every sense of the word, so i apologize in advance for any mistakes.

 

[lbass]

Can you explain what you hope to end up with? Do you want to be able to place each field (comma- or semicolon-separated) on the report separately? Do you also want to extract the Group A, Group B, BIBID, and BBM fields? What are the last two? Instances of Group B?

-LB

 

[mart10]

In the field we will place the following by default and let the user populate information:

GroupA:;
GroupB:;
GroupC:;
GroupD:;
BIBID:;
BBM:;

So the user must put information between the : and the ;
sometimes there will be no information so it will be left blank

For the Group fields each line may hold several pieces of infomation ie peoples names seperated by a ,

eg

GroupA:Bill Bloggs, Stan Smith, Ian White;


The aim is in effect to drag the data onto a report for each line

So i may need to drag on line 2 GroupB and show data associated with it

What I have to overcome is inability to create extra fields. Does this now make sense??

 

[lbass]

Why can't you just display the notes field as is?

-LB

 

[mart10]

because the notes field contains in effect several fields of data which needs to be shown in different part of report eg GroupA, BBM

I need to extract the data from this 'template' of data setup in the notes field.

 

[lbass]

Try using formulas like this:

extractstring({table.field},"Group A:",";")

-LB

 

[mart10]

The above works but what If I now want to go further and extract a certain position in the data from one of the rows

eg GroupB:123,289,uwu,628l,iii890dew;

and say i anted to extract the 2nd piece - 289, how would I do that and if a 2nd piece of data did not exist would it return null