InfoQube IM - Community

Submitted by jimspoon on 2017/08/10 11:29

Just curious. Say I wanted to extract some part of the text from one field of an item and put it in another field. Would it be possible within Infoqube to do this by using some sort of string functions (left, right, mid, len) or a regular expression? As a column equation? Or with some vbscript code?

Comments

Hi Jim,

Of course, you can do it in Field Properties (Auto-Assign or row equation) or using the Properties Pane

What exactly do you want to do ? Simple things does not require vbscript

Pierre_Admin

IQ Designer

Well, I would be using such a capability often. I have often thought I would have to massage my data in Excel first (using its very wide range of functions) before bringing it into IQ, but I am very interested to learn what and how I can do it in IQ itself.

Just for example. For years I have been preceding my notes with my own form of date/time stamp that would produce very easy sorting by date. For example "2017.08.10.2326 - " might precede a note and would indicate the date and time in a "yyyy.mm.dd.hhss - " format. This date would often NOT be the date/time of the creation or modification of the node, but rather the date and time that an event occurred, perhaps long before the note was created.

So for example, for all these notes, I'd like to extract this date/time stamp from my Item field, format it properly for IQ, and put it into a new IQ Date-type field, perhaps named "Event Date". Might be a date and time, or just a date, or even just a year where more specific date info is not available.

Could the resulting date/time value for each item be inserted in the new field as a static value, rather than as a dynamic formula (which might be recalculated if the date/time date from the source field were changed)?

I am very interested to learn what functions could be used in an Auto-Assign, Column Equation or in the properties pane as you say.

To accomplish what you want step by step:

1. Create the EventDate field

2. Create a VbScript function for the row equation of the EventDate field which takes the Item field as a parameter.

3. In this function you would need to extract the date and convert it to a date-time format.

4. Trigger manually the calculation for the row equation to extract all existing dates.

5. Remove the row equation from the EventDate field, so you don't have dynamic behavior after initial "import.

6. Create an auto-assign in the Item field and use the VbScript function.Think about your use cases - deleting the date from the Item field -> should it delete the value in the EventDate field or not?

thanks Ethanrox!

[quote]

[/quote]

Hi Jim,

For a static value, it is best to use the Properties pane:

Select 1 or more items
In the EventDate field, either enter VBScript to extract the date (such as Left([Item],10) ) or the name a User Script function name

If EventDate is a date field, it will update. If it is a text field you'll need to wait for v101 and add this step:

Right-click on the field and select Parse Equation

In your above example, you'll probably need a user function as there is too much variability in the text version of the date (with or without times, etc). It isn't hard to write, but if you need help, do not hesitate to ask

Pierre_Admin

IQ Designer