Extracting data from notes field

[ph2]

Please can anyone help?
I am using Impromptu ver6 to connect to an Access database to produce some reports.

One of the fields is a note field with free text - the content of this field is varied and in many cases is very large.

I need to be able to search this field for the value of say "Logged By" and then take the next 20 or so characters to appear as a column in a report.I do not want to list the whole field as it is to large and contains other information. Is there any way to do this?

 

[CognosProfessional]

Create a calculated column named "start" to get the character number that the word starts by:

position('Logged by' , your field )

In your filter designate that that calc. column named &quot;start&quot; is <> to the number '0'.

Create a calculated column named &quot;result&quot; with this expression:

right(your field , start )


Your result column should have the text starting at the character number that starts with the words &quot;logged by&quot;.

It just worked for me, and it should work for you too.


CP

 

[CognosProfessional]

If you only want 20 characters, create another column in addition to the above columns, and use this expression:

left(result , 20 )...
or %-)

CP

 

[ph2]

CP
Many thanks for your prompt replies.
I have tried what you suggested, but unfortunately I can not enter the field name. Impromptu does not list it as an option in either &quot;Report Columns&quot; or &quot;Catalog Columns&quot;.

According to the Profile, the column is a text field, but for some reason it is not available. I have tried your suggestion on another field and all worked OK, so I guess I am unable to achieve what I want to do because of this weird field?

Sorry, but I am fairly new to Impromptu.

 

[CognosProfessional]

Your field would need to be a text or string, and it should work just fine if it is listed in your catalog. Can you see the field in your catalog? if not, you need to add it. I don't know what you mean when you say it is not available. If it is in your catalog it should be available to you, if not, you need to add it to your catalog so you can choose it when you create a INSERT , CALCULATION from the INSERT menu. CP

 

[ph2]

Sorry if I was not clear - My field &quot;Notes&quot; is in the catalog. I can select it as a complete field in a report, but if I want to include it in any calculation, it is not available from the list. ie when I try to insert a calculation, everything is OK until I try to enter the field called &quot;notes&quot;.

I managed to get round this problem by saving a basic report that includes the &quot;notes&quot; field as a hotfile. I then inserted this into my catalog, which then enabled me to select it in calculations. The main problem I have now is that even though I uncheck the max 256 characters, only 256 characters are stored in the hotfile (the report that created the hotfile did show all characters)

This is turning into a nightmare - I really do appreciate all your help.

Paul.

 

[CognosProfessional]

I am still puzzled as to why you cannot choose a string item &quot;Notes&quot; for your calculation. If you can choose it to create the hotfile, It should be available for these calculations just the same. Unless it is not a string. If that is the case, you may need to use the functions necessary to make it a string. I would try this before using a hotfile.

If you really need to use the hotfile, then you could take your initial report and create 2 columns for the hotfile to split the large column by using a calc col. left(notes,256) for one column and then right(notes,256) for the second column. That way when you search you are getting a choice of 512 characters which should be enough. But,if not, you have to use more. I am curious as to how many characters are in the largest notes item in your database?
CP

 

[ph2]

I am puzzled too. This Access database is a shambles, for some reason someone thought it would be a good idea to store different types of data in one massive field ! Basically it is a helpdesk and this notes field stores history about the problem. If many comments/actions get added to a job - the field just gets bigger!

I have tried all different methods to convert to a string, but none seem to work.

Thanks for all your help on this one, but I think I have now got to the stage where I will just export the whole lot to Excel and manually manipulate the data.

Many thanks again for all your help

Paul