Get Chart Values

[theconav]

Hello everyone,

 

I have a chart and I want to update a table (in a different datapage) with the values of each of the chart's category items. The chart is the one in the attached file :

 

 

I need to be able to get the value of "Points" of each of the Category's items (ET, FB, G3, GA etc)  and update a different data page.

 

How could this be done ?

 

Many thanks !

[MayMusic]

There is a workaround though you can create a view based on Points_Table without any filtering so basically it is the same as the table and rename "Audit_Entity" in the view to for instance AuditEntity. Use the view as the source of the DataPage and now this formula should help:

 

SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity=[@field:AuditEntity]

 

When you change the table source of a DataPage you need to create the whole page all over to prevent that happening you need to remove the table name that is being added to the fields in the view.

[theconav]

Sorry, I think i found a workaround so, no reply is needed.

 

I have encountered though a different problem:

 

I have the following expression in a calculated field : SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity=[@field:Audit_Entity]

 

The Audit_Entity field is of type string and should therefore be entered using single quotes e.g. SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity='XX'

 

However, the problem is that you cannot include a single quote as part of the calculated field. Can someone suggest a solution please ?

 

Many many thanks

[MayMusic]

You should be able to use, single quote, double quote and also even without quotes. Are you getting any error messages?

[theconav]

Thanks a lot Emma for your prompt reply.

 

Well, when trying to use double quotes (e.g. SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity="XX") you cannot even exit the calculated field (in design mode) as there is an "Invalid formula" error with the description "Invalid column name RO".

 

Using single quotes (SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity='[@field:Audit_Entity]', literally takes the "[@field:Audit_Entity]" string as a value rather than the value of the Audit_Entity field and produces no results.

 

Using it without quotes ( SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity=[@field:Audit_Entity]) produces wrong results by totally ignoring the criteria set in WHERE  and this sounds logical as this would have made sense if the Audit_Entity field was numeric. However, this is a string field. 

 

At some point i suspected the error was resulting from the fact that the Audit_Entity takes its value from a dropbox, so I changed the dropbox to text and tried again  SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity=[@field:Audit_Entity]). The results were wrong, as it seems that it ignored the SUM and only displayed the value of the related record (only one record) of the Points_table.

 

I really cannot thing of any other approach, so any sort of feedback will be highly appreciated !

 

many thanks again !

[MayMusic]

What type is Audit_Entity and Points in your table? I am tryign to replicate it here

[theconav]

Thanks Emma,

 

Audit_Entity is text (value is available in a dropbox) and Points is number

 

Thank you

[ShWolf]

Hi theconav,

 

Try to use single quotes with static value:

SELECT SUM(Points) FROM Points_Table WHERE Audit_Entity='XX'

Seems this works well

[theconav]

Hi ShWolf,

 

Thanks for your reply.

 

It works perfectly when using a static value, I had tried that from the beginning. The point is that I I need to be able to get the result for each record of the table and since the Audit_Entity field has a different value in every record, a static value as the value cannot be used. 

[ShWolf]

Hi theconav,

 

Seems it's a bug.  

You can report it to the support team.

[theconav]

Thanks ShWolf,

 

I will do that