Masschanging elements checked in CardCollection checkbox

[w7_gmbh]

I feel like running into a barrier with what I thought was a super simple task, maybe the community can help and maybe this helps other community members.

First things first: I have a database in which I want to bulk update entries based on an input.
In my example, the bulk-update is about high-school students changing their class (basically being promoted to the next grade or changing classes 'sideways'). All students who have successfully completed the class can move up to the next grade so I want to be able to check multiple students and "promote" them, which means changing their grade in the database.

To check students I have a student query {{get_students_myclass}} which is displayed in a CardCollection ({{CardCollection3}}) and checked is naturally set false because users should select which students they want to promote.

Building on this article: How do I tell which CardCollection items are checked I was able to show the "promote" button based on items being checked or no item being checked.

I am also aware that this: {{cardCollection3.suffixValueByIndex[i]}} is a good way to get a boolean displaying which items have been checked.

Last but not least: {{select27.value}} is a select with all classes (1a-10d) to select where to promote the students.

The first part of my query is quite clear:

UPDATE student_db
SET student_class = {{select27.value}}
WHERE ID = ?

My question is:

How do I retrieve the ID from {{CardCollection3}} for items [i] given that for each item {{cardCollection3.suffixValueByIndex[i]}} is true (so it is checked) and use this in the query so it bulk updates the right entries based on ID?

Thanks in advance for any help!

[preshetin]

Hi @w7_gmbh - welcome to the community!

I enjoyed investigating your case. Thanks for sharing!

You may create a JavaScript query that would be triggered when a user presses Promote button. It may look like this:

// getStudents query initially gets records from students_db table and 
// is used as data source for CardCollection3
const students = formatDataAsArray(getStudents.data)

// This gets info about which checkboxes where chcked
const toBePromotedArr = cardCollection3.suffixValueByIndex

const studentClass = select27.value

const toBePromotedUserIds = students
  .filter((el, key) => toBePromotedArr[key])
  .map(el => parseInt(el.id))

await bulkPromoteStudents.trigger({
  additionalScope: {
    userIds: toBePromotedUserIds,
    studentClass: studentClass
  }
})

This JS query triggers another query of resource type that performs bulk database update. I named it bulkPromoteStudents. Before triggering that query, that JS query builds the list of student IDs that should be promoted and passes it that DB query.

The bulk promote query may look like this:

UPDATE
  mobile_app_sample_data
SET
  is_promoted = TRUE,
  student_class = {{ select27.value }}
WHERE
  id = ANY ({{ userIds }})

Some notes:

• For bulkPromoteStudens query it is better to set Additional Scope input field is filled with the value of the parameter so that linter does not complain.
• For JS query you may set couple of event handlers on success to trigger getStudents query and show notification message.

I'm attaching a few screenshots for better understanding:

Click to see screenshots

Hope this helps!

[preshetin]

Hey David - I wonder if you found my post helpful.

It contains a code block that can be overwhelming sometimes to process.

Let me know if you need any assistance

[w7_gmbh]

Sorry for being late with my reply, however, I was able to solve this with your help.

2 queries.

1. JS query, store the IDs of users/students that have been "checked"
2. SQL query to bulk-update the DB.

Query 1, update_students_getselected, I have added it to CardCollection3 as Event handler for Event "checkbox press":

const students = formatDataAsArray(search_students_classchange.data);
const toBePromoted = cardCollection3.suffixValueByIndex;

const toBePromotedStudentIds = students
  .filter((el, key) => toBePromoted[key])
  .map(el => parseInt(el.id));

return toBePromotedStudentIds;

Query 2, update_student_bulkpromote, I have added it to my button as Event handler on click

UPDATE student_db
SET student_class = {{select27.value}}
WHERE ID = ANY ({{update_student_getselected.data}})

This worked well for me - thanks for your help!

[preshetin]

Great to hear this @w7_gmbh !