Writing output line

[candyperry]

I need to have a sales tax line that is written to the "NewOrders.csv" just like the shipping line is written. When I run the code as attached, I get the results as in the sample file attached. Everything works fine except the very last records. The shipping line does not get written to NewOrders.csv at all for the last record.

Any suggestions?

[Tom Cone Jr]

Re: Writing output line

Candy,

This is difficult to troubleshoot without sample data, and a specific explanation of what's missing from specific rows in the output file.

In general I prefer not to rely on auto-type-conversion when concatenating data items of mixed data types. Recommend you convert each field value to character data when assigning values to the variables used to create the CSV file.

I do not understand the logic for the various code blocks you have defined in the script. As written it seems you are generating multiple lines from each record in the table. Is that intended? I'm confused by the various IF ... END IF code blocks, and the nesting of WHILE ... END WHILE blocks.

[candyperry]

Re: Writing output line

Sorry for the brief description. I had originally posted my issue back in 2012. Here is my original with more information: http://www.alphasoftware.com/alphafo...light=proc_num

Does that help at all?

[Tom Cone Jr]

Re: Writing output line

Helps a bit.

Please clarify. Is it the last row for each "Num", or the last row of the last "Num" that's missing?

Later ...

Sorry, your code has very little to do with the description in the 2012 message board thread. Please start over with your explanation.

tell us...

What goes in row 1
what goes in row 2, all the time, or only if ________?
what goes in row 3, all the time, or only if ________?
what goes in row 4, all the time, or only if ________?

If a row is suppressed, do you need a blank line to hold its place ?

And, importantly, supply sample data for us to work with.

-- tom

[candyperry]

Re: Writing output line

Thanks for the input. I've attached two files, one contains the original data before running the script and the other shows the result after running the script.

The last row of the table is the one that is not writing the shipping line.

Line 1= Customer 1 Item1
Line 2= Customer 1 Item2
Line 3= Customer 1 Sales tax, if any
Line 4= Customer 1 shipping
Line 5= Customer 2 Item1, etc.

If there is no sales tax, I don't want a blank line in.

Does this help?

-Candy

[Tom Cone Jr]

Re: Writing output line

Candy,

I've reformatted your code and then made one change, at line 63. My revised code is attached. You can import it into a sample copy of your database for testing.

I think your script failed to include the last shipping line because the IF statement at line 63 will always be FALSE when you "fetch" past the eof marker. i.e. before this IF statement is evaluated the script "fetches the next record". But when you try to fetch past the last record there is no "proc_num" field value waiting there to be evaluated. I changed the code slightly at line 63 to process the tax and shipping rows if the proc_num field is different OR if the record pointer is sitting on the last record of the table.

for background compare the discussion in the help system on these two functions:
tbl.fetch_eof() versus tbl.eof()

-- tom

[candyperry]

Re: Writing output line

Thank you so much! I will try this out and report back.

[Tom Cone Jr]

Re: Writing output line

Candy, I forgot to mention that for testing here I changed the path to the destination CSV output file at lines 6 & 7. hope that hasn't been giving you heartburn. Please test my changes using sample data and do not use it in your application until you're satisfied it does what you need. -- tom

[candyperry]

Re: Writing output line

That worked perfectly! Thanks so much. I'd be bald pulling my hair out if it wasn't for this forum :)

[candyperry]

Re: Writing output line

I just ran into a little problem. I find that when the last Num (order) contains more than one item, the shipping is added twice, once after each item. Do you have any thoughts about this?

[candyperry]

Re: Writing output line

I was wrong about how it was behaving. For the last Num (order) it writes two different shipping lines when there's more than one item. It looks like it adds one shipping row after the next to last item, then adds the last item as a row then adds the shipping row again. It doesn't add the shipping row after each item, just after the next to last item and the last item.

[Tom Cone Jr]

Re: Writing output line

I may be able to look at this tomorrow. Please furnish sample data that illustrates the problem.

-- tom

[candyperry]

Re: Writing output line

Thanks so much! Here are beginning and ending samples.

[Tom Cone Jr]

Re: Writing output line

Interesting logic puzzle.

Unzip the attachment to an empty folder.
Run the import operation to populate a temporary table
Empty table TPI-READY, and then fill it with the append operation.
Then run the make_csv script.

Hope this helps. In testing here it correctly handles the last order whether it has a single item, or multiple.

-- tom

[candyperry]

Re: Writing output line

Thanks so much. Time got away from me today, I'll check it out on Monday. Have a great weekend!