1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Updating fields in database row

[SOLVED] Updating fields in database row

4 years 4 months ago - 4 years 2 months ago #315811 by jimstrong
[SOLVED] Updating fields in database row was created by jimstrong
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?

Please Log in to join the conversation.

4 years 3 months ago #315825 by krileon
Replied by krileon on topic Updating fields in database row
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.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

4 years 3 months ago #315826 by jimstrong
Replied by jimstrong on topic Updating fields in database row
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.

Please Log in to join the conversation.

4 years 3 months ago #315832 by krileon
Replied by krileon on topic Updating fields in database row
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:
SELECT `DATE_FIELD_NAME` AS date FROM `#__comprofiler` WHERE FIND_IN_SET( `id`, REPLACE( '[CARER_FIELD_NAME]', '|*|', ',' ) ) > 0
Output: Multiple Rows
Row:
<div>[column_date]</div>

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:
$dates				=	array();

foreach ( explode( '|*|', '[CARER_FIELD_NAME]' ) as $carer ) {
	$date			=	\CBuser::getInstance( (int) $carer, false )->getField( 'DATE_FIELD_NAME' );
	
	if ( $date ) {
		$dates[]	=	$date;	
	}
}

return implode( ',', $dates );

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

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

4 years 3 months ago #315837 by jimstrong
Replied by jimstrong on topic Updating fields in database row
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.

Please Log in to join the conversation.

4 years 3 months ago #315839 by krileon
Replied by krileon on topic Updating fields in database row

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.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Updating fields in database row
Time to create page: 0.277 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. [SOLVED] Updating fields in database row

Search

Login / Logout

User Menu