Array format from webhook for multi insert

[athomas]

Good morning, I am catching a webhook with a database update that works well and a multi insert that I am unable to configure properly. I want to know what Wappler is expecting to receive in the database multi-insert "record" array variable and how to configure the database multi Insert. I have read, searched and experimented to solve this myself for many days. While I have tried many variations of arrays and objects along with Wappler multi insert configurations, I feel no love from the programming gods. Please guide me Oh Great Ones!

background info: node.js, Zeffy and Zapier integration for webhook, website is for car show registration and raffle ticket sales to raise money for cancer patient care. We have raised over $430,000.00 US.
labordaycarsforcancer.com

When functional, the database multi insert will insert between 1-4 rows with 7 columns each. "record" array is being received as follows:

["record":{"year": "1966","make":"Ford","model":"Fairlane","color":"Black", "class": "R) MuscleCar - Original", "engine": "5.0 Coyote", "mods":"SC"}, {"year":"2018","make":"Ford","model":"Expedition","color":"Silver","class":"R) Truck - Original","engine":"3.5","mods":"Lift"}]

Note: I have tried many configurations of array to no success. I have not tried single quotes but will by the time you read this.

I can write the whole record "record" to a single column in the database but cannot split the fields. The multi insert currently inserts 14 mostly empty rows.
I have tried $_POST.record.values() and $_POST.record.split() in the repeat properties. I think if given enough time I could unlock the secrets but

[Teodor]

Hello, how do you call this server action? You can use $_POST.record when the server action is called via server connect form submit. Is that the case for you?

[athomas]

Hi Teodor and thank you for your response. The configuration for the webhook is set to Post with payload of Form. I can select JSON as the payload if that will help. I am including an image below of the configuration.
URL
https://labordaycarsforcancer.com/api/receiveZeffyData

The form data arrives at my webhook but either the form data is malformed and not usable by my database multi insert or my configuration is wrong. I think I will build a multi insert form so I can see the form data configuration that the database multi insert can use so I can duplicate it.

Zeffy, the originator of the data sends the "record" form multi insert data as one string without name:value pairs as follows:
Raw string data-
"TicketAnswers":"66,Ford,Fairlane,Silver,J) Factory Muscle -Ford,5.0,Lots,2018,Ford,Expedition,Silver,R) Truck - Original,3.5,Lift"

I have put together some Javascript to format the data into something I thought Wappler could use for the multi insert based on the other form fields and research, displayed in the image as record data.

This is my first experience with webhooks and creating my own. It has been a learning experience. I did not use Stripe integration because this work is for charity so I am trying to avoid paying the processing fees.

Thank you,
Al

[baub]

Maybe your webhook isn't returning exactly what you think it is?

Try this, send your webhook to https://webhook.site/ And then view how the data is being returned outside of wappler.

[athomas]

Hi Baub, thank you. I have tried, I have a send mail set up with {{$_POST.toJSON()}} to see what was happening as well as many many attempts to a webhook test site Webhook.site - Test, transform and automate Web requests and emails

I then developed some javascript to mold the data into what Wappler seemed to want.
Finally I am hand crafting a form input for the record form variable. I will let you know how this goes. If I can figure out what form data works as an input to the database multi insert then I can craft javascript to produce what I need.

Javascript below for others trying to integrate with Zeffy through Zapier

// input two strings of name "userIDs" and value "car"
let { car, userIds } = inputData;

let results = [];

// Split key and value input strings for processing
car = car ? car.split(",") : [];
userIds = userIds ? userIds.split(",") : [];

// Hack the length of the names as a user can register up to 4 vehicles
// I could put the string in a variable and add accordingly - I am tired
if (car.length !== userIds.length) {
  userIds = ["year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods"];
} else if (car.length >= userIds.length) {
  userIds = ["year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods"];
} else if (car.length >= userIds.length) {
  userIds = ["year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods","year", "make", "model", "color", "class", "engine", "mods"];
}

// Make sure the strings are the same length
if (car.length !== userIds.length) {
  throw new Error("userIds number doesn't match the user IDs number");
}

// I tried the map function and may look into this further. It does not maintain 
//the order so I could not separate later into rows for the insert
//results = userIds.map((id, i) => {
//  return { [userIds[i]]: car[i] };
//});

// return name:value pairs as object
function createKeyValuePairs(keysArray, valuesArray) {
  const result = {};
  for (let i = 0; i < keysArray.length; i++) {
    result[keysArray[i]] = valuesArray[i];
  }
  return result;
}
const keyValue1 = createKeyValuePairs(userIds, car);

// return name:value pairs as array
var record = {};
userIds.forEach((key, i) => record[key] = car[i]);

// Returns string of name:value pairs    
const entriesArray = Array.from(Object.entries(keyValue1)); 

// Returns multidimentional array of name:value data with a row for every 
//vehicle registered and 7 columns of data
function arrayToMultidimensional(array, itemsPerRow) {
  const multidimensionalArray = [];
  for (let i = 0; i < array.length; i += itemsPerRow) {
    multidimensionalArray.push(array.slice(i, i + itemsPerRow) );
  }
  return multidimensionalArray;
}
const itemsPerRow = 7;
const record2 = arrayToMultidimensional(entriesArray, itemsPerRow);

console.log(itemsPerRow);
console.log(record);
return { record: record };

[athomas]

I sent some form data to my email and test site as follows:

{{$_POST.toJSON()}}

{"email":"[al.bigdaddy.thomas@gmail.com](mailto:al.bigdaddy.thomas@gmail.com)","fname":"allen","lname":"thomas","paidfor":"2","QRcode":"https://simplyk-bucket-production.s3.amazonaws.com/organizations/0/0/8/d/e-ticket/tickets-04c0dfcc-681f-441f-b9ca-8ab89d0510d5.pdf,https://simplyk-bucket-production.s3.amazonaws.com/organizations/0/0/8/d/e-ticket/tickets-04c0dfcc-681f-441f-b9ca-8ab89d0510d5.pdf","record":"[{\"year\":\"1966\",\"make\":\"Ford\",\"model\":\"Fairlane\",\"color\":\"Black\", \"class\": \"R) MuscleCar - Original\",\"engine\":\"5.0\",\"mods\":\"SC\"}, {\"year\":\"2018\",\"make\":\"Ford\",\"model\":\"Expedition\",\"color\":\"Silver\", \"class\":\"R) Truck - Original\",\"engine\":\"3.5\",\"mods\":\"Lift\"},]","tickettitle":"Cars for Cancer Event Ticket,Cars for Cancer Event Ticket"}

{{$_POST.record}}

[{"year":"1966","make":"Ford","model":"Fairlane","color":"Black", "class": "R) MuscleCar - Original","engine":"5.0","mods":"SC"}, {"year":"2018","make":"Ford","model":"Expedition","color":"Silver", "class":"R) Truck - Original","engine":"3.5","mods":"Lift"},]

From the webhook test site

[athomas]

The multi insert loops 14 times for the 14 pieces of data, 2 rows of 7, and inserts 14 lines into the vehicle table even though the multi insert record is structured into two separate arrays grouped together. The fields in the vehicle table are empty except the field VehMods. I have selected a value of {{$_POST.record}} for this column.

Should my database column names be the same as the Post variable names?
I know the form field names need to be the same as the Post variables.

[Teodor]

From what i see from the data you receive, you need to create a repeat picking $_POST.record as an expression.
Then inside the repeat (in the insert record) you can access its values as {{year}}, {{make}}, {{model}} without any prefixes.

[athomas]

Thank you Teodor, I have tried so many things. I believe the values in the record variable are not getting recognized as '{{year}}', {{make}}, {{model}} etc.

The repeat will insert a new record in the database at every comma in the $_POST.record. If I sent the $_POST.record a string divided by commas, the repeat will write a new row for whatever is between the commas.

I have tried to assign names to the values with some common ways but they are still not recognized. I have tried some different formats "key:value" pair and "key:value,value"

The following array will write half the data until the comma on one line and the rest of the data on a second line. See the image below the array.

{"year":"1966"}{"make":"Ford"}{"model":"Fairlane"}{"color":"Black"}{"class": "R) MuscleCar - Original"}{"engine":"5.0"}{"mods":"SC"},{"year":"2018"}{"make":"Ford"}{"model":"Expedition"}{"color":"Silver"}{"class":"R) Truck - Original"}{"engine":"3.5"}{"mods":"Lift"}

Below is the database updater settings that produce the above insert
I have assigned the values without any prefixes. It seems the values are not getting into the record variables.

Thank you for your help,
Al

[athomas]

Hi guys, what I really need to know is in what format the $_POST.record variable is expecting the data. Then I can make it work.

What format is the data that will work with the multi Insert?

The rest of the webhook works well and the $_POST.record is populated with whatever I put in there through the webhook. The rest of the fields of the $_POST.record variable ie: $_POST.record.year, $_POST.record.make, $_POST.record.model etc. are not populating.

I have tried many data structures that will assign them to the correct $_POST.record variable but I have not realized this effort.

Thanks again,
Al

[Teodor]

@athomas

{"year":"1966"}{"make":"Ford"}{"model":"Fairlane"}{"color":"Black"}{"class": "R) MuscleCar - Original"}{"engine":"5.0"}{"mods":"SC"},{"year":"2018"}{"make":"Ford"}{"model":"Expedition"}{"color":"Silver"}{"class":"R) Truck - Original"}{"engine":"3.5"}{"mods":"Lift"}

That is no correct.
The data needs to be an array:

"your_array": [
        {
            "year": "1966",
            "make": "Ford",
            "model": "Fairlane",
            "color": "Black",
            "class": "R) MuscleCar - Original",
            "engine": "5.0",
            "mods": "SC"
        },
        {
            "year": "2001",
            "make": "Some make",
            "model": "Some model",
            "color": "Black",
            "class": "some class",
            "engine": "3.5",
            "mods": "abc"
        }
    ]

[athomas]

Hi Teodor, and everyone, thank you for your help. I sent the array to the webhook and it wrote 14 mostly blank lines in the table. The following screenshots will help explain if I am configured properly. You will notice I have currently 1750 rows of data in my database table attempting to figure this out. I am not asking for help facetiously and I appreciate the knowledge and skills of this group.
NOTES: The $_POST.record variable is populated with the array but the repeat step ignores any array formatting and fires at every comma like a string.
Question, should the record array variables have the multiple property checked?
The ~record variables are not populated (year make model etc.) and I have hundreds of attempts to make the ~record variables populate from the $_POST.record through different array formats and key:value pairs.
I am going to delete my multi-insert steps and associated variables. I will recreate this section and report back. In the mean time, any suggestions are welcome. Thank you, Al

[Teodor]

So is it actually an array or are you sending a string to the server action?
Please disable the insert step and add add a set value step in the repeat. Select any of the bindings from the repeat - make, model - whatever you like. Enable output and see in the browser dev tools under XHR what is the result, when the server action runs? Does the set value output anything?

[athomas]

It is an array. I sent your code exactly as received. I have tried every structure but the named variables under record do not populate and the repeat (loop) triggers on every comma so it writes blank information.

Something must have happened to the repeat or without an app side it does not have the ability to handle the array data.

I will report back once I recreate the multi insert and retest.

Thank you

[athomas]

I will do as you ask first

[athomas]

Teodor, nothing is returned XHR. I re-enabled the database write and those values are there. Nothing from the repeat. I also sent record variables to my send mail and they are blank. I chose from the non repeat and the repeat area and nothing.

[Teodor]

Is there any way i can test this? Something is obviously not correct with the data you receive from the POST.

[athomas]

Yes, I will send user name and password to the zapier website that is sending the data. How would you prefer I send these?

I looked more closely at the email data after I sent your array. In the $_POST.record formatted section it looked fine but in the {{$_POST.toJSON()}}
section it was displayed as follows.

I know the backslash is ignored but still a problem may exist.

"record":"[\n        {\n            \"year\": \"1966\",\n            \"make\": \"Ford\",\n            \"model\": \"Fairlane\",\n            \"color\": \"Black\",\n            \"class\": \"R) MuscleCar - Original\",\n            \"engine\": \"5.0\",\n            \"mods\": \"SC\"\n        },\n        {\n            \"year\": \"2001\",\n            \"make\": \"Some make\",\n            \"model\": \"Some model\",\n            \"color\": \"Black\",\n            \"class\": \"some class\",\n            \"engine\": \"3.5\",\n            \"mods\": \"abc\"\n        }\n    ]"

I am not sending the array as a JSON.

As you pointed out, I am suspicious now about how the array may be arriving to the $_POST.record variable. I will dig deeper.

[Teodor]

You can send the login details and explanation how to recreate the issue as a personal message here in the community.

[Teodor]

Ok i checked your use case, indeed the value received is not what the repeat expects. You will need to use the parseJSON() formatter on the repeat expression. This formatter is not available in the UI, but you can manually add it like:

$_POST.record.parseJSON()

in the data picker:

Then the repeat will work as expected.