Grabbing a value from grid and adding it to a column in an imported Excel file

[asully]

Hello All,

Got a bit of a head scratcher here and need some help from stronger coders. I've got a grid that manages invoices and has detail views. I've got an action button that opens another grid for serial number tracking (linked on 'Invoice_id' field). Any new row automatically gets the 'Invoice_id' field (hidden) filled with the linked value. There are two other fields: Model_number and Serial_number. Users can manually enter models and serials and they will be connected to the current invoice selected and ultimately tied to the importer, date, manufacturer, etc., through the invoice data. Some manufacturers send us Excel sheets of the serial numbers on a given shipment so I want to be able to import that sheet as an alternative to manual entry. I have an action button on this grid to accomplish this using the 'Upload Excel/ASCII File and Import' action javascript. The Excel sheet has two columns: Model_number and Serial_number. I add an Invoice_id column to the sheet before I try to import it for the following reason: I want to be able to add the current Invoice_id to each row programmatically through the validate function so the data is always connected to the invoice just as if it was entered manually. I also use 'honor grid rules' in order to boot out any duplicate serials trying to be added to the table. Below is the simple code in my validate function:

function AddIDField as v (e as p)

debug(1)

dim t as p
t = table.open(e.inputFilename)
t.batch_begin()
dim invidval as c
dim i as n 
dim count as n 
invidval = Request.variables.V.R0.INVOICE_ID
count = t.records_get()
for i = 1 to count 
        t.change_begin()
        t.Invoice_ID = invidval
        t.change_end(.t.)
        t.fetch_next()
next i 
t.batch_end()
t.close()

end function

When I run the code through the debugger in Working Preview I can step through it and watch as 'invidval' is correctly assigned the value of the 'Invoice_id' from row 0 of the grid and is written to each row. It works exactly as I want it to, importing the records to the table and adding the Invoice_id value to that field for each row of data. Awesome! Right? Not so fast...when I upload to the server and run it there I get the following error:

Error executing Validation Xbasic event handler: AddIDField Error message: command: invidval = Request.variables.V.R0.INVOICE_ID Property not found

There has to be a simple way to do this - any help is appreciated.

Part 2 of this question would be - how can I programmatically ad the 'Invoice_id' column to the temporary table created upon excel importation and then add the Invoice_id value to that field. I've watched the videos and I've put in the code in order to do this but it also gives me an error code when trying to run it:

fields = <<%str%
Invoice_ID,c,16
%str%
'table.add_fields("table1", fields)
Table.Add_Fields(e.inputFilename,fields)

The error is: Script:a5_add_fields_to_table line:147 Variable append.m_exp1 expression - Field is not recognized: '@FILE_9189CF7CCC5F46CB82E1260378CD7927_TEMP->MODEL_NUMBER'.

MODEL_NUMBER is the first actual column heading in the imported excel sheet so, I'm not really sure what's happening here and thus I removed that code and manually add the 'Invoice_id' column to the sheet and leave it blank...but I would prefer to add the column programmatically. Any help?

Thanks gang.

Sully

[lgrupido]

Re: Grabbing a value from grid and adding it to a column in an imported Excel file

Not sure I completely understand what you are doing, but ...

I think you are trying to do too much during the import. I would import the spreadsheet AS IS into a temporary table. Then you can link that temporary table to other tables to get other values (like the invoice ID).

Since you are using DBF files (I think), that would mean creating a set between the temp table and your invoice table.

Then you can just copy rows from your set into whatever final table you want.

I am not an Alpha DBF guy, so hopefully the idea will translater. I do all my work in SQL, but the concept is the same.