Can i assign an id based on fields in a table being imported

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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.  

Photo of Nancy

Nancy

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Let me see if I can summarize your requirements. You want to import your data and assign an invoice # for each line in the CSV file so that each unique combination of location and cost center has the same invoice # and the invoice # should start counting from the largest value already in the table. 

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!
Photo of Nancy

Nancy

  • 0 Points
Yes that is exactly it and summarizes it quite well.  I've been using Quickbase for about 1 year and only pretty basic needs thus far.  really appreciate the help, good luck with the snow, I don't miss that stuff.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Okay I create some an application with some pre-existing data (that would be consistent with having been previously imported):

Modify CSV Before Import
https://haversineconsulting.quickbase.com/db/bkjx4krrt

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 #.
Photo of Nancy

Nancy

  • 0 Points
I'm excited to look at this but I'm not following the "Button in the Tasks table"  I don't see a tasks table, there is a table #1 or am I taking your text too literal.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Look again as I am building it:

https://haversineconsulting.quickbase.com/db/bkjx43e4k?a=td

It does not do anything yet as I write the code and test it from the JavaScript console first and then will port it to the application. I am just showing you the navigational structure of how you can organize and run various tasks within an application.

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!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Photo of Nancy

Nancy

  • 0 Points
it's prompting me to login
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
That's odd - I already set permission to everyone on the internet. I will deal with this later after I finish the demo.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Here you go:

Modify CSV Before Import ~ Tasks Table
https://haversineconsulting.quickbase.com/db/bkjx43e4k?a=td

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".

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=473


Notes

(1) This demo uses the technique described in this post to organize tasks that will run specific scripts defined in the application:

https://quickbase-community.intuit.com/questions/1300721

(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
https://haversineconsulting.quickbase.com/db/bkjx4krrt?a=dbpage&pagename=downloadCSV1.js

Download Sample Dataset #2
https://haversineconsulting.quickbase.com/db/bkjx4krrt?a=dbpage&pagename=downloadCSV2.js

Import CSV
https://haversineconsulting.quickbase.com/db/bkjx4krrt?a=dbpage&pagename=ImportAndProcess.js

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.
Photo of Nancy

Nancy

  • 0 Points
wow - this is amazing.  One question - the link https://haversineconsulting.quickbase.com/db/bkjx43e4k?a=td is still prompting me to login so I can't see the app.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
> is still prompting me to login so I can't see the app.
Does anyone else have this problem? Attached is a screenshot of the permissions. Maybe I am overlooking something.
>wow - this is amazing.
More to come in 2016 that is amazinger!
Photo of Nancy

Nancy

  • 0 Points
what's the difference between this link you had originally in the thread:https://haversineconsulting.quickbase.com/db/bkjx4krrt (this opens an app) and this one which is the most current link https://haversineconsulting.quickbase.com/db/bkjx43e4k?a=td (this prompts me to login)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
One was a like to the app homepage and the other was to the Tasks dashboard. If you are in you are in. Maybe I had a typo somewhere ...
Photo of Nancy

Nancy

  • 0 Points
I can get in with the first link but I don't see a tasks dashboard is just the Default Dashboard with List All
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Okay I fixed it. I imported (ie moved) the Tasks table from a master application and apparently the permissions were not what I expected. It should work now and you are free to import and purge records. Everything should work mindful of the fact that others may be using the application at the same time because it is so amazing.
Photo of Nancy

Nancy

  • 0 Points
I see it now - thanks! and it is amazing
Photo of Nancy

Nancy

  • 0 Points
Hi - i'm getting really close to getting your code to match up with my tables but I'm struggling with the code that starts with   $.when(promiseInvoice, promiseD3).then(function(argsInvoice, argsD3) {
            var maxInvoice = parseInt($("invoice__", argsInvoice[0]).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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
"invoice__" represent the XML tag QuickBase associates with the field [Invoice #] by lowercasing and substituting an underscore for non-alphanumeric characters. Note there are two underscores - one for the space and one for the #.  If you have a different field name you need to substitute. A "console.dirxml(xml)" command would help you debug and see the full XML response and in particular the tag name used for the invoice number field.
Photo of Nancy

Nancy

  • 0 Points
thanks - i thought that was what it was but didn't know about the lowercase and underscores.  
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The CSV blob is malformed  because it has an internal comman that  is not withing double quotes.

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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I updated the application and pastie as well as added a third test case of data. This third test case deals with fields that have commas or double quotes within them. Before being added to the CSV blob every field has double quotes replaced with two double quotes (QuickBase's requirement) and all fields are surrounded with double quotes so that internal commas will no longer be interpreted as delimiters.

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.
Photo of Nancy

Nancy

  • 0 Points
Thanks so very much!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
BTW, did you gt this thing working for you? The reason I ask is that someone else has a similar need to "Modify CSV Before Import " by adding a column to the CSV representing the current users primary role. You might chime in here if the news is positive:

https://quickbase-community.intuit.com/questions/1305798
Photo of Nancy

Nancy

  • 0 Points
Yes - it worked great.  based on the full scope of what I needed to do I made several changed but your code is the foundation and it works perfectly.  thanks for teaching me A LOT.
Photo of Nancy

Nancy

  • 0 Points
Hi Dan - one quick question.  I assume that if the user can use the tasks code to import the data, there is no way that I can also prevent them from editing the data in the tables is there?  They need to be able to view but I'd prefer to not have them edit but I think I have to because they need that access to do the import but thought I'd check  thanks