Sage 100

Attention all VI experts... v4.3 advanced.... Need to export from the AR Invoice History Header and Detail. This is a shipment confirmation file to be transmitted to CommereHub. There is supposed to be 3 record types in the file: 1 header record and 2 detail records (message detail and packaging detail). How can I create a VI job to export 1 header and 2 detail records?

I'd avoid doing this with VI, and use Crystal instead. VI takes forever to generate the data and Crystal offers much better control over parameters and selection formula. Here's a simple one I created a couple years ago. Tap into AR_InvoiceHistoryTracking to get tracking numbers.

I don't know that I would use VI for this Doug. VI can pull the data out, no problem, but it naturally wants to pull record for record. I mean , 1 header record is going to export 1 record(not 3 different records). If I had to do something similar I'd probably produce the export file with a more sophisticated query tool. Maybe MS Access, I might even just right a script myself that read the data I needed from history and then create and export the header and 2 detail lines in the script. Just trying to give you some direction. You could even produce a crystal report that you export and send to CommerceHub.(Not the most automated solution.)

Thanks guys. Great advice. What worries about Crystal is this reads the SO Invoice header and detail. These are huge tables and sending shipment confirms could take some time. Not much choice though.

500K records usually takes about a minute to generate file. I have this set to generate daily file unattended, so speed not an issue.

It's faster than using VI. You could also use Access with Pass Thru Queries or SQL with a linked server.

If you are still having performance problems another trick you can try is use MS Access or SQL as Robert suggests, but pull only the subset of the tables (i.e. Limit the number of records and the number of fields to what you need.) into a local table in MS Access using a Make Table query. Then use MS Access or Crystal to pull the record layout you need.

I cheat and create a UDF in the table to be exported with the file assigned value of the field I need from another table. :)

All great ideas. Thank you. I am going to create a quick crystal report and see what if it is acceptable. If not, I will move on to the other options.