Tuesday 29 March 2011

Parameters in Dollar Expansion

I had come across this subject in the documentation before but never had a use for it - until today.

I was faced with several fields that each had a code and description embedded in them that needed to be parsed out. Generally, I would just use SubField() for this but unfortunately the separator was a period (.) so I couldn't be sure to get the full text out using SubField because the text could contain periods (a period is not a good choice as a separator!)

I decided that the easiest thing to do would be to use the Left() and Mid() functions in conjunction with the Index() function. This started looking like this:

Left(Field1, Index(Field1, '.')-1) As Field1.Code,
Mid(Field1, Index(Field1, '.')+1) As Field1.Desc,

There were 5 fields involved and I wondered if there were a different way that I could go about it. I re-checked the help file on Dollar Sign Expansion and came up with this:

SET mLefty=Left($1, index($1, '.')-1);
SET mRighty=Mid($1, index($1, '.')+1);

The $1 in this variable is a parameter. So I can pass a parameter to a variable!

Now I can use these "functions" in my code:

Field1, $(mLefty(Field1)) as Field1.Code, $(mRighty(Field1)) as Field1.Desc,
Field2, $(mLefty(Field2)) as Field2.Code, $(mRighty(Field2)) as Field2.Desc,
Field3, $(mLefty(Field3)) as Field3.Code, $(mRighty(Field3)) as Field3.Desc,
Field4, $(mLefty(Field4)) as Field4.Code, $(mRighty(Field4)) as Field4.Desc,
Field5, $(mLefty(Field5)) as Field5.Code, $(mRighty(Field5)) as Field5.Desc,

Now this is not something that is incredibly advanced - but I can imagine a lot of possibilities.