Please excuse me - I forgot to mention that this is FMPA 11
Might be easier to offer some help if you are able so post your excel file
The data structure above is the result of a custom function which captures (on a per record basis) only those fields that are actually populated...
Some of the import tables have a massive amount of columns - and looping through every field of every record did not make sense.
This resultant data structure was fine until some clients started including CRs in the data.
So, to actually post the Excel file would not make any real sense, as this data structure presented above is the end result of some time saving scripting.
Hope that makes sense...
I suggest you try to construct a calc that temporarily substitutes some other character likely to be unique (eg. the pipe character "|") while you complete your selection, then replaces it with the CR when you complet your processing. You should be able to do all of this within a single Let( ) caclulation.
I think I tried this, however, substituting at the field level is not an option.
Substituting within the Variable does not help either...
For exmple, If I were to substitute the CRs for Pipes in the above data, I would get :
<Business Area>Retail|<Business Status>Active - 24/7|<Business Scope>All ENC|<Business Approach>Cold|<Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...|Customer Details|Customer Address|Customer Search|Customer Security|Contact History|<Business Name>Offer of the Day|<Business Status>In Development|<Business Type>Desktop Application|<Business Version>4.6
In other words, ALL the pairs would be effected, and I would still be and I would still be left with the original problem ... which is trying to differentiate paired values from line values
It sounds like you already have a script that generates the list from the excel spread sheet. Can you have it add a unique character set at the end of each pair? Say "|*|"
So now you would have something that looks like
<Business Area>Retail|*|<Business Status>Active - 24/7|*|<Business Scope>All ENC|*|<Business Approach>Cold|*|<Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless..|*|Customer Details|*|Customer Address|*|Customer Search|*|Customer Security|*|Contact History|*|<Business Name>Offer of the Day|*|<Business Status>In Development|*|<Business Type>Desktop Application|*|<Business Version>4.6|*|
Next replace (Subsitute) all of CRs with "" or " ". Then replace |*| with a CR. Now you would have a list of pairs you can loop through.
You can then use GetValue[loop counter] to get the pair. in a set variable statement you can turn the pair in to a list by substituting ">" for a CR. GetValue(1) gets the field and GetValue(2) gets the data.
I think this is what Keywords may have had in mind.
Your Customer pairs and Contact pair may be a bit more tricky. You can look at the first characher or leftwords functions to determine these.
"I think this is what Keywords may have had in mind."
That was the idea Bruce. Thanks for the amplification. If you are sure that this is the only pair affected you could even particularise the calc for the "business description" pair.
Many thanks for the input ...
I cannot be sure that only one pair will be affected "
due to the CR seperated values which are part of <Business Description> in this example - and could be part of any of the values really." - please excuse my rotten english.
I'm trying this for the moment ...
<Business Status>Active - 24/7
<Business Scope>All ENC
<Business Description>This could well be some text, with some return seperated data also, making using the GetValue function useless...
<Business Name>Offer of the Day
<Business Status>In Development
<Business Type>Desktop Application
<Business Version>4.6" ;
$_c = PatternCount ( a ; "<" ) ;
_c2 = $_c + 1 ;
op = "<" ;
cl = ">" ;
pos1 = Position( a ; op ; 1 ; $_c ) ;
pos2 = Position( a ; cl ; 1 ; $_c ) ;
pos3 = Position( a ; op ; 1 ; _c2 ) ;
$_label = Middle( a ; pos1 + 1 ; pos2 - pos1 - 1 ) ;
$_data = Middle( a ; pos2 + 1 ; pos3 - pos2 - 1 )
$_c will be reduced every loop -
Seems to work - as long as there are no "<" OR ">" in the data :-)
Again, many thanks for your input
Take a look at the attached file. It uses the initial text I suggested with the |*| character set as a field delimiter. Puts everything in a list and sets fields for the Business fields. I left out the Customer and Contact ones because they looked like field names with out values.
Open the file and click the Parse button.
I hope this clears up what we have been trying to suggest.
Parsetext.fmp12.zip 67.5 K
this works fine - thank you very much.
Using a slightly different Substitute I like it - many thanks indeed