Using a query as a condition for an update query

[iamsoops]

So, mine isnt showing the query only the final error. I have debug enabled on the query, am I missing something?

Update: FYI Query condition is removed

[famousmag]

[iamsoops]

OK, so Ive disabled those two steps but now nothing is showing in browser console:

However, I can see in the wappler logs that it runs the query and then forwards tothe success page. Do I need to disable the action to forward to the success page?

[famousmag]

I don't know what happens but we need to see the output...
Can you take care of it?

[iamsoops]

I really appreciate all your help.

I'll see what I can work out this evening, but I dont really know what to try now

[iamsoops]

Maybe I will try and push the query into a csv file and we can view the output that way. But I fear its going to be the same issue as pushing it into the array and will not work.

Maybe I need to totally rethink the design. I honestly thought this would be simple.

[famousmag]

we just go step by step my friend...

First, we have to get some data... Do we have the data or our query doesn't work correct (error or returns no results)

Then we will take care of the next step, the Add In ArrayList

But just a note...

1. I don't know if just by creating an ArrayList without defining its schema will work (maybe it does, but I'm not sure)
2. If all you do is add all the records from the query into the ArrayList... why not using directly the query?

[iamsoops]

So, on that last point I previously had a forum post about that and came to the conclusion that you cannot next queries and used the array as the intermediary step:

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 outcod…

My very first approach was exactly that and I thought it would be quick job. Use the query to populate the subtable but it seems that cannot be done that way.

As you can see, I have been working on this one issue for weeks now and have tried everything

[iamsoops]

Opening the api directly in the browser gives this response but still doesnt show the query response in the browser console.

Any clues here?

[iamsoops]

OK, now I have unticked debug I am getting an output when loading the API directly. Let me know if this helps

In the meantime I will remove debug. I assumed that was for additional logging, but maybe thats causing the issue. I'll report back (school run now)

[iamsoops]

Hi @famousmag , I have made some progress over the past few hours. Also thank you to @franse as I have made some good progress using the IN query type and removed the requirement for the second array (providers)

My current position now, is I have fixed 2 outcodes into the outcode_array for testing. I know this works as I have tested previously. So, to avoid having to execute the form each time they are hard set.

This is working well and they are picked up from the outcode_array in the query via the IN method. You will see in the video that we get the full provider lists that are expected. Even more, I have been able to push the provider URNs into a new table. I am so close now thanks to everyones support. However, in the seperate table I do not have a link back to the main enquiry table (hence originally wanting to use subtables). Video below showing everything working. For info I have tried to get this to work with a subtable for hours and cannot get it to work, hence looking for a workaround.

So, my workaround would be as follows.

1. Have an insert query to add the enquiry to the master enquiry table (postcode, name etc)

2. Somehow grab the UUID from that first query - Can I do this?

3. Run a second query (exactly as per the video) but pass the UUID from the record added in the previous query and manually enter that into the table so I have a link between the 2

Thanks everyone for all your help... are we close?

[franse]

Hey @iamsoops
Glad you made some progress here

@iamsoops

However, in the seperate table I do not have a link back to the main enquiry table

Now you lost me haha
My lack of english is enough to misunderstand what exactly do you need

If it's not too much to ask, can you explain step by step (including the code api thing) what is your project about?

I also see some static value on the add in array, it's a correct way?

[famousmag]

OK, looks better now...

If you want to create a replicate of a query but add to it an extra (or more fields/columns) you can do that with ArrayLists.
But you have to define the ArrayList schema. In the schema you add all the columns you need from the query plus the extra columns you need (for example inc_UUID -text).
So inside a repeat based on the query, you add a ADD TO LIST step and in the panel you define the fields you need by binding the value you want (for example inc_UUID=xxx)

You say "fisrt query" and "second query" and "record added in the previous query" and I cannot understand...
If you need to get the primary key (id) of a database insert you can bind the identity value from this insert:

This identity gives the value of the inserted record id

I'm sorry if I don't give you clear suggestions but I haven't understand correctly the whole proccess.

[iamsoops]

@franse @famousmag you guys are amazing

This has really opened up a lot of options for me

I have now successfully added a record to the enquiry table, used the identity tag to then add multiple records to a seperate table with the linked identity and each of the provider URNs (these are pulled from the provider table via the outcode method)

Thanks so much for all your help to get to this point and @Apple too on my previous posts. I was very close to giving up on this project but have learnt so much!

My one last question, is here I have the 2 tables linked via the indentity, is there any disadvantage to doing this as 2 seperate tables rather than a table/subtable as I was originally planning? (I have tried to use this method to insert into the subtable and I cannot get that working). @Hyperbytes, as a database guy I'd appreciate your view on this if you dont mind?

I'll now go back through and re-introduce the api elements and get the POST form working for the data (currently I have hard set the add arr_outcodes entries. Not expecting this to be a problem as it all worked before.

[Hyperbytes]

@iamsoops

Hyperbytes, as a database guy I'd appreciate your view on this if you dont mind

Can i see the full data structure similar to your first post but with the sub tables also expanded

[iamsoops]

Hi Brian, thanks for replying.

See below the original target design of enquiries / enq_providers subtable plan, there isnt much too is as you will see. I just want to avoid boxing myself in down the line by not using subforms at this stage.

Currently I am achieving this with 2 insert queries to 2 seperate tables and linking using the identity value as directed by famous mag.

I'll be using this data to present to both providers and the enquirers the status of their requests via multiple dashboards.

Outside of this I have a couple of other tables for user credentials and payments etc. But I didnt want to over complicate

[Hyperbytes]

Thanks, will see what i can feed back

[Hyperbytes]

Regarding the providers table outcode field, this is an array or comma separated list or single entry?

[iamsoops]

The outcode field in the providers table is a single entry. Every provider has only one outcode.

Here is an example record

[Hyperbytes]

I think i still need what i first asked for, a screenshot of the Database manager with ALL the tables expanded.
I an hopfull this can be done in an entirely different and easier way with a single custom query