[SOLVED] Updating fields in database row

Caveat:- I am not a professional programmer, but have learnt my way around basic SQL and have also been using Community Builder for some years. I am hoping that I can stick with this combination.

I am tasked with setting up a database for a charity whose requirement is for an Admin to enter and edit user records on behalf of each user. Some users are allocated as Carers for other users (Clients) and the charity needs to track what these allocations are and when they were allocated.

I am trying to find a way to update fields in a database row / record for the Carer, triggered by the entry of data in the row/record for the Client.

So far, I have set up a multi-choice field so as to give each user a title of “Client” or “Carer”.

Then in User1’s (the Client’s) CB User record, I am entering the name of User2 (the Carer) selected from a Check Box (Multiple) field with a DB Query list and also entering a date in a “date first allocated” field.

I am then trying to achieve the User2 (Carer’s) record’s similar date and Client field to be automatically updated with the same data, thus linking the two users and allowing a search to be carried out - e.g. having a List that shows all User2 (Carers), the User1s (Clients) they are allocated to - and when they were allocated.

I have purchased a professional membership recently in the hope that CB Query Fields and CB Auto Actions would allow me to achieve this, but I appreciate that perhaps I am trying to force Community Builder/Joomla to do too much and there are other more elegant solutions using the relational capabilities of SQL?

Ok, so you want to pull the Date field of the Carer to the Clients profile. Do you need this for display purposes only or do you need the date field also stored in the clients profile? If it's display only it's rather simple implementation using CB Query Field, but if you need to replicate the data from Carer to Client it gets more complicated in needing to use CB Auto Actions to synchronize the data.

I'm not fully clear on the difference between storing and displaying, but I think display will be fine - but let me expand:- When the Admin opens either the Client or Carer profile (or looks at the user record in the back end) they will see the date displayed. Similarly, when they display a List on the front end they will see the date displayed in both the Carer and Client Lists.

The main issue is you state the Carer is a multi-checkbox field? Is it intentional for the user to select more than 1 carer? That will complicate the usage, but it's still doable. The below Query field for example should work for what you're wanting.

Query: Output: Multiple Rows
Row:
Be sure to replace DATE_FIELD_NAME with the actual name of your date field and replace CARER_FIELD_NAME with the actual name of your carer field. That should output a list of dates based off who was selected for carer, but because you're using a multi-checkbox field the query isn't going to be inefficient. It might be better to use a Code field instead with the below PHP.

Code:
As with the query usage be sure to replace CARER_FIELD_NAME and DATE_FIELD_NAME.

Oh , thanks very much for those suggestions and code - so relieved to read that it is relatively simple! I will give it a try.

> Is it intentional for the user to select more than 1 carer?
That is how it has been described to me, but I will go back to the charity and enquire that it is definitely a requirement.

Thanks again.

That is how it has been described to me, but I will go back to the charity and enquire that it is definitely a requirement.

Both the above examples SHOULD work with selecting multiple carers. I recommend the Code approach though instead of the SQL one as the SQL one is inefficient SQL and there isn't really a way to make it efficient due to how multi-checkbox fields store their values.