Database insert into table and sub table

Hi,
I'm a newbie here and despite looking at a number of the tutorial videos and walkthroughs I'm struggling to get my insert to work across both tables and subtable.

Use case is an enquiry is raised by an individual and I am then polling postcode.io via their API to get the local postcode outcodes that are nearby to them

Everything works gathering the postcode data and I can put the first response into the main table without issue. However, when I add the subtable and try to push the outcodes into there (as there could be multiple outcodes per enquiry) I just cant make it work. (ignore my type of calling them endcodes in the naming convention :slight_smile: )

Here is my main insert:

Here is the element for the sub table:

I feel like this is something obvious but cannot get anything into the subtable at all.

Any supprot welcomed

Hopefully, this will help:

Basically, I think what Ben wants to say is you can't directly input an array into a subtable (like you did). You have to use a Repeat step with a Database Insert step (known as Multi Database Insert)

Alternatively, instead of a subtable, you could use a Json/JsonB column

1 Like

I don’t think that’s correct, but I’m too lazy to get off the couch and confirm right now :sloth:

I believe I have API’s that build an array on the server side and then include in a sub table insert. What I recall is that you must include all the necessary columns the table expects, without renaming and without adding anything extraneous.

But like I said I’m basing this on what may be outdated knowledge.

Thanks for the responses everyone. I'm just considering my options here, so have a couple of follow on questions.

Ref the form repeat option. I've read through and understand the overall method. However, my repeat data will be called from the outcodes.io api and the user doesnt need to see it. Would this method work if I entirely hid the repeat form and populated it via the api. I assume I could use a dynamic update on the postcode field to run the relevant api and populate the form each time.

Ref storing a JSON string within the database field. This could work as its not going to be a lot of data. Max 6/8 outcodes. However, I am unclear on the steps to retrieve the data as a JSON from the database, Is that straightforward, my next step is to query my provider list by outcodes, so I'll need to build fther queries off that array?

In summary, this is what I am trying to do. The user only provides the postcode

Thanks again for all the reponses.

Are making an API Action directly on the page? My advice is for an API Action inside a Server Action, where there's no repeat form, but a Repeat step

3 Likes

Thanks Apple. Yeah, still working through and appreciate your points.

At the moment I am focusing on the JSON field in the same table approach and have been able to get a single item from the API in that field now I've set it up as and JSON array in the database.

I'm now trying to understand how to get the repeat element to work. Here is my current flow, I have attempted to count the total items in the api call array and then tell wappler to repeat that many times but it doesnt seem to be putting anything into the database now.

I'm aware I am way off now, but does anything immediatly jump out on this screenshot to give me a pointer?

I'm 5+ hours into this now and I thought it would be straightforward :frowning:

Your Repeat step looks very weird, it shouldn't have another API Action inside.

Also, you're Repeating the .count(), so it'll iterate numbers from 0 to x instead of iterating the actual postal codes

1 Like

Thanks Apple. I think I get the way the repeat is working now and might be getting warmer. But I am still now getting nothing added to the array field:

I wonder if I should have 2 seperate api calls that run one after another when the enquiry is submitted. Initial one to create the record and a second to then do the update. Would that be better? I guess I'd still hit this repeat issue on the update action.

You can see I have tried to create a variable to temporatily hold the array before the insert then picks that up. But its still not coming through.

FYI the list items are all disabled if you cant tell

Looking at the logs, I think its doing the repeat correctly. I just cant seem to get that into the DB field

Current setup:

I just realized the problem :laughing:

You're supposed to do:
For each postal code (?), you need to Database Insert

What you're doing:
For each postal code, you're adding it to the array
Basically, you have an array, and you're iterating through the array, and adding the elements to a new array. So you're basically doing nothing interesting

I'm used to the old tree view, so thanks for the clarification

Man, I'm so stumped. Just cant get it to work :frowning:

The API works fine if embedded in the front end, just getting nothing when I try the repeat. Does anyone have a similar example they could share. I was expecting this to be a half hour job and Ive spent the whole bank holiday on it!

I guess I could do something in the front end to sort it there, but I should probably get my head around the issue if I am going to get much use out of wappler

Sample of API data source in front end working fine:

Personally, I would start on what is working at the moment, as in

After that, I would take a short nanna nap to clarify a more elegant solution as in

Just my humble opinion :beers:

Morning, me again :slight_smile:

I decided to leave this issue for a day or two and work on some other areas of the app and after some thought decided to simplify things down to a basic level. With that in mind, I am trying to isolate the repeat element.

Below, you'll see that I have disabled the email elements and the existing insert (which works fine)

I've created a pair of env variables to use as a count for the repeat. They're manually set at 0 for the count and 4 (the count would ultimately be the count from the API call in final state) for the total.

My intention is to repeat the process 4 times incrementing the count variable and inserting a database entry each time. However, I'm not getting any inserts. Have I misunderstood how to structure the repeat/define the repeat criteria?

All the variables are set to numbers and to output at each stage. As I write this, I'm wondering if I need a condition in there somehow, but I cant see how that would work with the repeat?

As ever, any thoughts gratefully received. :pray:

NB: Just added a couple of comments in to see if they show up in the debugger to assist. Will add that in if I can find anything of use

Looks like I have worked it out.... I needed a while literally and metaphorically :rofl:

image

image

Hope thats of use to someone else in future

Yes you can! With a single insert and a sub table when using the form repeat

See:

1 Like

Hi all. Made loads of progress on this and have setup a repeat that creates an array and then a single insert query adds a record to the main table with multiple entries into the subtable. Great!

As I am working in baby steps, I'm now trying to extend this to add a pair of entries to the subtable and am hitting an issue when adding the fields to the array list, it seems they wont save. Ive used the same method that works for the single array list and that works fine.

Either there is a reason this cant be done and Wappler is stopping me or there is a bug. As you will see in the video I cannot get the add in array list to hold the settings. (I've restarted wappler and recreated the add array list action, but no luck). Can someone advise?

Full API view for reference:

Don't use Name and Global Name with the same value at the same time. You can leave it empty:

image

1 Like