Exporting line items

Is there a limit on exporting line items in Pan x? The database in question has 517 fields, 480 of which are line items. I didn’t write the original; just trying to pick up the pieces.

The following worked perfectly in the database in Panorama 6 but in Pan X it throws an error message of Export Formula Error “Field or Variable [DescΩ] does not exist.” .

Export “PNLI.txt”,DescΩ+¬+¬+¬+¬+¬+¬+str(PricΩ)+¬+¬+¬+Firm+¬+Customer+¬+datepattern(«Date»,“mm/dd/yy”)+¬+¬+¬+str(«Invoice #»)+¬+¬+¬+¬+str(NumΩ)+¶

Desc1 is the the first field in the collection. If I replace it with a tab, PricΩ doesn’t exist. It is the second line item.

Has anyone encountered this?

In previous versions of Panorama the export statement had special handling if the formula contained line items – it would output each line item on a separate line. The Panorama X documentation says that this works, but the code was never written. There is only a “to-do” note in the source code saying that this has not been implemented yet.

This feature must have been very rarely used. In the eight years since the first Panorama X beta was released, I can find no record that anyone has asked about this before. It’s not in the issue tracker, and I don’t see it here on the forum. Since this would be quite an involved feature to implement, I may “fix” it by changing the documentation to indicate that this feature is not available.

There is a proposal on my to-do list to possibly create a new statement that would export line items as separate records to another database. This would be an expansion of Panorama’s relational features, possibly even configured with the Relation panel in the Database Options dialog.

For now, however, there is no one step method to perform this operation. You’ll have to write your own code to do this.

Bummer. I used it constantly and I’m sure others did as well when using line items in Panorama before version X. I must admit, I haven’t used line items in a long time and apparently no-one else is using them either.

Thanks for letting me know that it’s worth my time trying to troubleshoot it.

Sorry, it’s not worth my time.

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.

Thank you John, but I rewrote the procedure in about 20 minutes using a much less complicated method. It’s not worth my time was a correction of my first reply.