My first webhook: capturing initial data uploaded from CSV import. How to edit multiple records?

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

Probably a two part question:

1. Uploading a schedule via CSV import. I want to capture the initial completion date on the schedule, so I can monitor for changes. Some items are added to the schedule without a completion date set yet. Aha, I thought, a great opportunity to try a webhook, since I can't use a form rule.

(Q1: is a webhook the best solution?)

2. I am not an API guy, but I was able to learn and build the webhook. Here is what I've got, filling fid_35 with [Prod Ship Date] using XML API_EditRecord

<qdbapi>

%RepeatOn%

<usertoken>%user.token.53i_XXX.SalesToken%</usertoken>

<rid>[Record ID#]</rid>

<field fid="35">[Prod Ship Date]</field>

%RepeatOff%

</qdbapi>

You'll notice the Repeat on/off tags ... I have no idea what to do with them. They don't cause an error in this case, but they don't seem to work. Testing finds that only one record gets updated. Leads to two questions, then:

Q2: What is the format of the RepeatOn/RepeatOff and where should they go? I don't see much to help me with this.

Q3: Will the trigger be evaluated for each record? I suspect it only is done for the first record it encounters. (Mine tests to see if the target field 35 is empty.)

Or is there a fancier way to code the webhook that I wouldn't be aware of?

Thanks for the help.

Photo of David

David

  • 836 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I don't think that a webhook is practical for a multi record operation.  Webhooks are "gated' to run a max of I think 10 in a second.  So you cannot expect to import 100 records and have a process done to them automatically by a webhook which updates a record at a time.

However, a webhook could call a saved table to table import with API_RunImport&ID=10.  That saved Import could import the table into itself to map one field into another.  Is there a clean hook in your date so that Saved Import could know which records to update?
Photo of David

David

  • 836 Points 500 badge 2x thumb
Don't think the CSV sync likes API_RunImport: "You can't import into the Schedule table because it is connected to another application, service, or CSV file. Data can not be imported into it. "

From a day-to-day sync process, I'm not at much risk of 10 records at one time, but I get your point. Doing this at the source data (excel) seems like a reach to do, as much as with QB.

The basic logic I want is: If [init sched] is empty and [ship date] is not empty, then put [ship date] in [init sched]. Feeling stumped.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
OK, Plan B.

Change the name of the [init scheduled] field to be [init schedule(raw CSV import)].
Make a new field called [Init sched] as a formula date field with a formula.:

IF(IsNull([init sched]) and not isnull([ship date]), [ship date], [init schedule(raw CSV import)])
Photo of David

David

  • 836 Points 500 badge 2x thumb
Evil trick of formula self-referencing blocked by QB.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
??
Photo of David

David

  • 836 Points 500 badge 2x thumb
You are suggesting creating formula [Init sched] which contains IsNull([init sched]). No can do, unless you meant a different field.

Also, didn't understand the point of renaming [init scheduled] unless you are just identifying it as part of the import.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Right, I guess it needs to be

IF(IsNull([init schedule(raw CSV import)]) and not isnull([ship date]), [ship date], [init schedule(raw CSV import)])
Photo of David

David

  • 836 Points 500 badge 2x thumb
Mark, thanks for your help, but I still don't seem to be getting anywhere. I feel confirmed that my concerns about this issue, at least. I only have one incoming field of [ship date] that may get repeatedly updated. There is no separate data of 'initial schedule' to import ... just the first time the field has a date, that is the initial schedule. For years a thorn in my side for this excel report being given to my department and you had to pull up a previous copy of the report to figure out what dates changed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I think that I need to understand a bigger picture of your relationships and tables here.
Photo of David

David

  • 836 Points 500 badge 2x thumb
There are no relationships. Part of the challenge is that the CSV-sync table can't easily link to other order data. But I think I'll have to revisit that, that may be the sanest solution. Its fun banging against the limits of QB, tho. Thanks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Maybe that is the issue.  If there was a link to a parent table, then you could just summarize, say the most current status up to the parent table and there could be some logic up on the parent record to decide which date to use.

Is there a reason that this import table cannot be a child of the orders?  Does it know the order#?
Photo of David

David

  • 836 Points 500 badge 2x thumb
Yea, that's what I'm revisiting. I have the order 'lines' elsewhere in QB with the official/initial schedule date, but the incoming CSV data doesn't have line numbers listed and instead has orders by individual units (compared to a line which may list an order for two units). In the case of a multi-line order, I'd be making QB guess which items go with which line. I have set up a report-field that helps bridge the gap, but it is a poor substitute for an actual relationship. There are other benefits if I can link the CSV table to the orders, I'm going to see if I can figure out another way.
Photo of Eric

Eric

  • 40 Points
API_AddRecord is used for single record changes, and API_ImportFromCSV is used for multi-record changes. If you're importing you need to build a CSV file to pass to the API with your collective changes. Here's how I created mine:

<qdbapi>

<usertoken>USERTOKENREMOVED</usertoken>

<records_csv>

<![CDATA[

%repeatOn%

[old.FieldName1.csv],[old.FieldName2.csv],[old.FieldName3.csv],[old.FieldName4.csv],[old.FieldName5.csv]

%repeatOff%

]]></records_csv>

<clist>22.16.18.88.12</clist>

</qdbapi>
Photo of David

David

  • 836 Points 500 badge 2x thumb
Thanks Eric. I see that API_ImportFromCSV can edit, not just add. I'd need to filter the CSV list and trigger it via a webhook still, I don't see why a webhook wouldn't work. For now I've worked this problem out via more social engineering, but will keep this in mind.
Photo of Eric

Eric

  • 40 Points
The API_ImportFromCSV will only add records if you omit the key field for that table as one of the columns you utilize