Having a calculated field in a data page display multiple results in one cell

[KenWi]

Is there any way to have a calculated field in a data page display multiple return values from a select?

I have this

But where that query should return 5 items it is only displaying the first one, Can I concatenate and iterate in some fashion? I was expecting the entire result set to appear.

 

[PotatoMato]

Hi @KenWi, you can use XML path to return multiple values that meet the condition. For example:

STUFF((SELECT field_name FROM table_name WHERE field = '1' FOR XML PATH ('')), 1, 1, '')

 

[KenWi]

15 hours ago, PotatoMato said:

Hi @KenWi, you can use XML path to return multiple values that meet the condition. For example:

STUFF((SELECT field_name FROM table_name WHERE field = '1' FOR XML PATH ('')), 1, 1, '')

 

That seems to be getting me closer, as it will stuff a big block of XML in the cell. However using the SQL

Select DISTINCT Preceptor_Name_FullName from _v_M3_Placement_View where 
((Placement_Placement_ID = '[@field:Placement_Placement_ID]' AND Preceptor_Join_Placement_IsPrimary = 0 AND Placement_WeekNumber = '[@field:Placement_WeekNumber]') )

causes an issue as soon as I add FOR XML PATH (''), because at that point the system seems to lose any concept of [@field:Placement_Placement_ID].

Any experience on how to keep the variables I need to reference for the query? Possibly defining some intermediate variables?

[PotatoMato]

What do you mean by "lose any concept of [@field:Placement_Placement_ID]"?

[KenWi]

7 hours ago, PotatoMato said:

What do you mean by "lose any concept of [@field:Placement_Placement_ID]"?

The XML returned is effectively the same as if the query was.

Select DISTINCT Preceptor_Name_FullName from _v_M3_Placement_View where 
(Preceptor_Join_Placement_IsPrimary = 0)

The 

Placement_Placement_ID = '[@field:Placement_Placement_ID] and

Placement_WeekNumber = '[@field:Placement_WeekNumber] 

seem to be being ignored, or evaluating against every possible value for the fields.

Removing FOR XML PATH ('') will result in the table displaying the proper number of Rows, but every row is individual and contains the first element of the expected result set.

[CoopperBackpack]

Hello @KenWi,

Please test this formula:

STUFF((SELECT DISTINCT  ', ' + (Preceptor_Name_FullName) 
       FROM _v_M3_Placement_View
       WHERE Placement_Placement_ID = target.[@field:Placement_Placement_ID]
       AND Preceptor_Join_Placement_IsPrimary = 0 
       AND Placement_WeekNumber = target.[@field:Placement_WeekNumber]
  FOR XML PATH ('')), 1, 1, '')

If it doesn`t work, I think it is better to contact Caspio support since it is easier to troubleshoot the formula when there is access to data.

[KenWi]

On 9/26/2024 at 4:35 AM, CoopperBackpack said:

Hello @KenWi,

Please test this formula:

STUFF((SELECT DISTINCT  ', ' + (Preceptor_Name_FullName) 
       FROM _v_M3_Placement_View
       WHERE Placement_Placement_ID = target.[@field:Placement_Placement_ID]
       AND Preceptor_Join_Placement_IsPrimary = 0 
       AND Placement_WeekNumber = target.[@field:Placement_WeekNumber]
  FOR XML PATH ('')), 1, 1, '')

If it doesn`t work, I think it is better to contact Caspio support since it is easier to troubleshoot the formula when there is access to data.

Thank you again, that worked perfectly and I would have never have thought that I needed to isolate the field using ()s. 

[KenWi]

Just a tack on, to have this format properly it needs to use a 1, 2 at the end of the STUFF function to remove the additional space. But the isolation of the return type is what did it.

[telly]

Hi @KenWi,

Just want to add a helpful reference regarding to formula Stuff https://howto.caspio.com/function-reference/#:~:text=Stuff(string1%2C start%2C length%2C string2) and https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server