1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Query Field with sql math functions and cb_field values

[SOLVED] Query Field with sql math functions and cb_field values

8 years 4 months ago - 8 years 4 months ago #275872 by uncleron12345
[SOLVED] Query Field with sql math functions and cb_field values was created by uncleron12345
Hello,

I need to calculate the value of a field automatically.
The calculated value depends on some basic math functions and two other cb_fields.

My current query is:
SELECT cast((228+(('cb_calc_anzahlmitarbeiter'*228)/40))+('cb_calc_anzahlazubis'/1000)as decimal(6, 2))
FROM   `#__comprofiler` 
WHERE id=[user_id]

The given result is always "228" :pinch:
But when I replace the 'cb_fields' with "normal" numbers, it works. So it seems I simply do not get the field values.

How can I get the numeric values of these two cb fields in query to get the correct results?
I don't know what I've overseen :silly: :silly:


Thanks in forward :)

Please Log in to join the conversation.

8 years 4 months ago #275882 by krileon
Replied by krileon on topic Query Field with sql math functions and cb_field values
You're using single quote (') instead of back quote (`). That means it's seeing those field names as literal strings instead of as table columns.

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.

8 years 4 months ago #275888 by uncleron12345
Replied by uncleron12345 on topic Query Field with sql math functions and cb_field values
thank you very much.
How could I have overlooked that? : D


In this case I have a further question.
Isn't it possible to get a value in a query field from another query field?

"cb_calc_anzahlazubis" is a field which counts all active users with profile type "a". So i created a query field which selects all "approved" where profiletype=a.

But i can't access the counted users field directly in my query field above.
I had to create another field which sets the value via auto actions.

When I try to calculate with the first query field i get an 1054 Error - Unknown Column.
Is this right or do i have missed something?

Please Log in to join the conversation.

8 years 4 months ago #275897 by krileon
Replied by krileon on topic Query Field with sql math functions and cb_field values

Isn't it possible to get a value in a query field from another query field?

Yes, you can use substitutions in the query field query. Both "Raw Field Value" ([cb:userdata field="FIELD_NAME" /]) and "Original Field Value" ([cb:userfield field="FIELD_NAME" /]) usages should work.

When I try to calculate with the first query field i get an 1054 Error - Unknown Column.
Is this right or do i have missed something?

Query fields don't have storage so they don't have a database column.

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.
The following user(s) said Thank You: uncleron12345

Please Log in to join the conversation.

8 years 4 months ago #275898 by uncleron12345
Replied by uncleron12345 on topic Query Field with sql math functions and cb_field values
great!
thank you! :)

problem solved :)

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Query Field with sql math functions and cb_field values
Time to create page: 0.251 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Developer Members Support
  5. [SOLVED] Query Field with sql math functions and cb_field values

Search

Login / Logout

User Menu