We import time (hours worked by staff) data from excel weekly which adds to existing data. We need to assign an invoice ID to the newly imported records. I only have this one table. First I need to know the max invoice ID from the existing data and then I need to assign an invoice ID to the new data. I've found a few posts to do this but this is where I my need is different, the data being imported will need to be assigned different Invoice IDs based on the location and cost center which are 2 fields in the data, when those values change, the next invoice ID needs to be assigned. Example
assume the max invoice id from the existing data is 100500
new data example:
Loc a, CC 1, worker mike - should get Inv # 100501
Loc a, CC 1, worker steve - should get Inv # 100501
Loc b, CC 1, worker john - should get Inv # 100502 because the loc and cc is different
The user is only importing the data and then printing the invoice report (one invoice per page), they are not editing or adding and data. I could have them go to a form and complete an action as long as it didn't have to be done for each record and could be done in bulk.
Is this correct? If this is correct it is easy to do with script and I will create an example for you while to snow accumulates.
BTW, I have been using QuickBase for about 15 years and I have never found a well formed problem that could not be solved with script. All hail script!
Modify CSV Before Import
The button in the Tasks table will throw up a small dialog where you can select a CSV file and the new records will be imported assigning the desired Invoice #.
BTW I work with a modified pomodoro timer so I am just putting a few minutes in here and there when I have free time. It will be done before the snow ends!
Modify CSV Before Import ~ Tasks Table
To test the application first have a look at the existing records and note (1) the number of records and (2) the highest [Invoice #] value. Next, click on the button "Download Sample Dataset #1". Finally, import the file ImportMe.csv that you just downloaded by clicking on the button "Import CSV".
(1) This demo uses the technique described in this post to organize tasks that will run specific scripts defined in the application:
(2) There are three Tasks defined for this demo. Two of the tasks downlaod sample dataset that can be later imported into the application using the third task. Here are the names and URLS for the scripts:
Download Sample Dataset #1
Download Sample Dataset #2
The ImportAndProcess.js Script
(4) Two promises are defined in the script. The first promise load the D3 library which has a nice CSV parser we will use. The second promise queries for the largest [Inoivce #] within the table.
(5) A jQueryUI Dialog is constructed with has a File selection control and Proceed and Cancel buttons.
(6) When the file is changed a FileReader object is created to read the contents of the CSV file as a string and the rows of the CSV string are parsed into an array of arrays using the D3 CSV parser.
(7) Using Underscore's groupBy method the unique combinations of [Location] and [Cost Center] are identified and iterated over. During the iteration a CSV blob is created that includes an incremented [Invoce #] and values for the [Location], [Cost Center] and [Worker] fields.
(8) When the Proceed button is clcicked the newly formed blob of CSV data is imported using API_ImportFromCSV
(9) The page then redirects to the List All report grouped by [Creation Date]. The most recently imported data should appear at the bottom of the report in the last group of data.
var maxInvoice = parseInt($("invoice__", argsInvoice).text() || "0", 10);
I'm not getting the right maxInvoice value - what does 'invoice__" represent? should maxInvoice at this point actually hold the mx invoice value from the existing data? Mine is not, it's 0.
one more question if you don't mind. My name field is actually "Last, First" and in the console xml it's looking right with all being in one field but when it actually goes into the table it's ignoring the quotes and splitting at the last and first names in to 2 different fields.
Use this line of code instead:
csvLine.push('"' + (index + maxInvoice) + ',"' + field.join('","') + '"');
There is one more change to make in the event your fields contain a double quote but I will make that change in the morning. I have not made these changes to my code you but I will put it on my todo list.
You can alsays do console.log(csvData), console.log(csvLine) etc to see what the script is calculating.
Everything should work now. It is worth mentioning that someone imported tab delimited data which the application did not support (nor am I going to make a change). I simply deleted this data.