Skip to Content Skip to Menu

Field Groups and dates

  • rbuelund
  • rbuelund
  • OFFLINE
  • Posts: 565
  • Thanks: 40
  • Karma: 4
4 years 2 weeks ago #321295 by rbuelund
Field Groups and dates was created by rbuelund
I would like to know if the following is possible:

I have a field of type Date. This field I have added to af field group. On every user I add 2 - 3 dates in this field group. Now I wanna make a list where I only show users with a date that is inside the span of the actual week i watch the list.
So if I view the list in week number 46 in 2020, users wich contain dates between 9/11 and 15/11 should be shown in the list.

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48548
  • Thanks: 8290
  • Karma: 1445
4 years 2 weeks ago #321305 by krileon
Replied by krileon on topic Field Groups and dates
Field groups are stored as JSON in the database. You can not perform complex searches or filters on them. They are only JSON searchable if your database is at least version 5.7.8 otherwise they are stored in Text columns instead of JSON columns and their searching becomes very basic. If you are using MySQL 5.7.8 or greater then configure your field group field to be searchable and add it to a userlist where you should then be able to search it.


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 or Create an account to join the conversation.

  • rbuelund
  • rbuelund
  • OFFLINE
  • Posts: 565
  • Thanks: 40
  • Karma: 4
4 years 2 weeks ago - 4 years 2 weeks ago #321310 by rbuelund
Replied by rbuelund on topic Field Groups and dates
I do not wan't to search the list I want to filter the list, with the advanced filter.

The database i use is version 5.7.31. Now I tried to use this advanced filter:

WEEK(`cb_teammode`) >= WEEK(CURRENT_TIMESTAMP)

the field cb_teammode is the single date field that is grouped in the field cb_teammoder, which I want to show in the list. One of the dates is today 09/11-2020 - but nothing is shown in the list ??
Last edit: 4 years 2 weeks ago by rbuelund.

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48548
  • Thanks: 8290
  • Karma: 1445
4 years 2 weeks ago #321315 by krileon
Replied by krileon on topic Field Groups and dates
To filter on fields in a field group field you need to use JSON functions to extract a value. See the below regarding using MYSQL JSON functions.

dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

Field groups use the following JSON structure.

Code:
[ INDEX_1 => { FIELD_1_NAME => FIELD_1_VALUE, FIELD_2_NAME => FIELD_2_VALUE, FIELD_3_NAME => FIELD_3_VALUE }, INDEX_2 => { FIELD_1_NAME => FIELD_1_VALUE, FIELD_2_NAME => FIELD_2_VALUE, FIELD_3_NAME => FIELD_3_VALUE }, INDEX_3 => { FIELD_1_NAME => FIELD_1_VALUE, FIELD_2_NAME => FIELD_2_VALUE, FIELD_3_NAME => FIELD_3_VALUE } ]

Example data as follows.

Code:
[ 0 => { "cb_text" => "hello", "cb_date" => "1990-01-01", "cb_checkbox" => 1 }, 1 => { "cb_text" => "neat", "cb_date" => "1990-01-02", "cb_checkbox" => 0 }, 2 => { "cb_text" => "fun", "cb_date" => "1990-01-03", "cb_checkbox" => 1 } ]

The index is the row number. Rows begin with 0. Inside each row is its fields stored by field name. If I want to get the value of cb_text from only the first row my extract would look like the below.

Code:
JSON_EXTRACT( `FIELD_GROUP_NAME`, '$[0].cb_text' ) = 'ABC'


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 or Create an account to join the conversation.

  • rbuelund
  • rbuelund
  • OFFLINE
  • Posts: 565
  • Thanks: 40
  • Karma: 4
4 years 2 weeks ago #321317 by rbuelund
Replied by rbuelund on topic Field Groups and dates
Wou!! I believe there is something seriously wrong with the Field Groups plugin !!! I just wanted to se the data stored in the field through my phpmyadmin, but I was not able to show the _comprofiler table at all!! Then I deleted the Field Group field in CB, and voila, I can see the _comprofiler table in phpmyadmin, added the field back again, and the view is gone again. If I try to access the column attributes for a random column in the _comprofiler table I get a bunch of MySQL errors, thoose errors also go away if I delete the field in Community Builder again:

Warning in ./libraries/dbi/DBIMysqli.class.php#262
mysqli_query(): Unknown type 245 sent by the server. Please send a report to the developers

Backtrace

./libraries/dbi/DBIMysqli.class.php#262: mysqli_query(
object,
string 'SELECT * FROM `holside_dk_db`.`hol141_comprofiler` LIMIT 1',
integer 0,
)
./libraries/DatabaseInterface.class.php#183: PMA_DBI_Mysqli->realQuery(
string 'SELECT * FROM `holside_dk_db`.`hol141_comprofiler` LIMIT 1',
object,
integer 0,
)
./libraries/tbl_columns_definition_form.lib.php#405: PMA_DatabaseInterface->tryQuery(string 'SELECT * FROM `holside_dk_db`.`hol141_comprofiler` LIMIT 1')
./libraries/tbl_columns_definition_form.inc.php#68: PMA_getMoveColumns(
string 'holside_dk_db',
string 'hol141_comprofiler',
)
./libraries/structure.lib.php#2470: include(./libraries/tbl_columns_definition_form.inc.php)
./tbl_structure.php#79: PMA_displayHtmlForColumnChange(
string 'holside_dk_db',
string 'hol141_comprofiler',
NULL,
string 'tbl_structure.php',
)

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48548
  • Thanks: 8290
  • Karma: 1445
4 years 2 weeks ago #321319 by krileon
Replied by krileon on topic Field Groups and dates
What PHP version are you on? Your database management software is out of date on an out of date PHP version and isn't aware that JSON columns exist. You should be on PHP 7.x by now and running latest compatible phpmyadmin. I can't do anything about you using out of date phpmyadmin that has no idea JSON columns exist. To be specific your problem is with phpmyadmin not CB or its database.


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 or Create an account to join the conversation.

Moderators: beatnantkrileon
Powered by Kunena Forum