Understanding how to update multiple records in serverconnect

Wappler General Need Help
1

I’m trying to do some data conversions as I’m merging two databases. What I’d like to do is create several serverconnect files which go through each record in a table, I’d like to be able to use the values in the record to update a value if certain conditions exist. The conditions could involve doing another database search on another table looking for certain values in other fields. It seems that server connect could do this but I just don’t know the syntax. Any more documentation on database multiple update?

Thanks,
Rick

2

Hello Rick,

So you don't want users to input the changes, but just run the action which checks the records values?

One thing i don't understand is, where does this condition come from:

3

Maybe it would be possible via triggers or stored procedures directly on DB Layer?

4

Sound like a multi stage server action with a lot if loops. You may find custom queries the best route rather than lots of loops. Some examples could help me be more specific

5

Is this a one-time thing? Or are you planning on doing it more in the future?

6

Here is an example. I have 2 tables. One for events and one for projects for the event. The project records have an event_id field to reference which event the project belongs to. The projects table has a categories field too for differnt types of project like photo or video coverage. The events table has an event_type field for the kinds of events like weddings, corporate etc.

I didnt have the event_type field for older projects so that field is empty. I would like to have an action which sets the event_type field to ‘Weddings’ in the events table gor all events records where there is a project in the projects table with category equals ‘wedding photography’ or ‘wedding videography’.

I have an action that can do this but for one record which is run from an edit button on a page where there is a table of all events with an edit button in every record. I can click on the event and the action determines if the event is a wedding or not and sets the event_type correctly. I just would like it done to all records instead of pushing the edit button for all 1000 records. Does that make sense.? I think i just need to know how to use repeat in a server action where the repeat condition is a database query of the events records. Just dont know how to reference each record in the repeat loop. I just cant find any docs about repeats in server actions which go into that type of detail. Thanks for all your help as always.

Rick

7

You should probably take the SQL query route for that as already suggested above.

8

You should be able to do this by using a query and a repeat with an update step inside.

  1. Query your projects table, filter the query by the category column, using two conditions connected with the OR operator:
  • equals wedding photography
  • equals videography

  1. Repeat step, using the query as an expression (just select the query from the data picker)

  2. Inside the repeat add update record step:

  • Select the events table
  • Add the event_type in the columns list and manually enter the value Weddings
  • In the update conditions tab, filter by the event_id column and select the event_id returned by your repeat step as a value.

The repeat step will only return the event_id which the query returns, and you already filtered the query in step 1 :slight_smile:

9

this works beautifully. WOW. I was able to update quite a few fields and clean up my data using this technique. Thanks.

1 Like