It may not be as complicated as you fear. Writing a generalized solution for exporting any combination of line item fields, as Jim would have to do to match Pan6 behavior, is daunting. But producing a unique solution for a know case need not be. I never trying exporting line item fields in Pan6 so am not sure precisely what that code would have produced then. But if I’m interpreting your post correctly, all that line item field data could easily be reorganized into a form from which you might be able to see a fairly quick path to the finish.
You mention total of 480 line item fields and your export formula specifies three sets of them: DescΩ, PricΩ and NumΩ. So I’m guessing fields Desc1 to Desc160, Pric1 to Pric160 and Num1 to Num160. Conceptually you could combine each set of 160 into a three new fields containing text arrays via
AddField "DescAll"
Field DescAll
FormulaFill lineitemarray("Desc",";")
AddField "PricAll"
Field PricAll
FormulaFill lineitemarray("Pric",";")
AddField "NumAll"
Field NumAll
FormulaFill lineitemarray("Num",";")
Above separates each entry with semicolons but you can specify any other separator, eg. cr()
or lf()
, which makes sense to you. Lineitemarray
( function automatically converts numbers into text for you.
If you wanted your final data to record from which # of line items it originated then change the 3rd line above to
FormulaFill seq()+":"+lineitemarray("Desc",";")
Those three new fields could be combined into another as a 3 by 160 two dimensional text array with each line containing Desc:Pric:Num and separated by semicolons.
AddField "LinesAll"
Field LinesAll
FormulaFill arraymerge(DescAll, arraymerge(PricAll, NumAll, ";", ":"), ";", ":")
If including which line item numbers, each line of the combined field contains #:Desc:Pric:Num, a 4 by 160 text array.
Or you could skip the 3 intermediate fields and create the 3 by 160 text array in one step
AddField "LinesAll"
Field LinesAll
FormulaFill arraymerge(lineitemarray("Desc",";"), arraymerge(lineitemarray("Pric",";"), lineitemarray("Num",";"), ";", ":"), ";", ":")
or to include the line item numbers
AddField "LinesAll"
Field LinesAll
FormulaFill arraymerge(seq()+":"+lineitemarray("Desc",";"), arraymerge (lineitemarray("Pric",";"), lineitemarray("Num",";"), ";", ":"), ";", ":")
The above added fields could be worked into your export statement’s formula. Separating the steps into adding extra fields makes the above easier to conceptualize, but isn’t necessary. You could skip the added field calculations and code the lineitemarray(
and/or arraymerge(
functions directly into the export statement’s forumula.
With a LinesAll field you could keep your original 37 non-line item fields, delete all 480 original line item fields and have all your information organized into just 38 fields.
If you wanted to strip out blank line items add
Field LinesAll
FormulaFill arraystrip(arrayfilter(LinesAll,";",{?(import() contains "::","",import())}),";")
If you want each trio of used line item fields to result in one line of output substitute lf() for the “;” separator make a LinesAll field as above then use as your export statement
export PATH, arrayfilter(LinesAll,lf(),{OutputForumula}), SEPARATOR, ENCODING
ENCODING could be left blank.
SEPARATOR could be blank also or could be lf() to produce an additional blank line after each original records set of lineitemfields
OutputForumula is whatever formula of your non-line item fields plus the Desc, Pric and Num trios you want exported. With the latter being specified by array(import(),#,“:”) functions for which ever # column of LinesAll data you wanted there.
Understanding the re-organization may take longer than typing the code to produce it. If all you want is to maintain access to old data a PanX database with the original 37 non-line item fields, plus one LinesAll field full of text arrays, is apt to run and display quicker than the orginal 517 field design and contains all its data. PanX can slow down when there are lots of fields, hence its user adjustable limit of, IIRC, 150 fields. However, if you intend to use this to add new data, perhaps leaving the 180 line item fields for data input yet adding a LinesAll field for data export may be simpler.
I’ve done no testing on this so some tweaking may be required, but if my guess above as to your original layout is correct it should be fairly close.