Hi everyone,
I’m currently experiencing a technical issue regarding the synchronization of calculated data between two entities in a Table Widget.
Current Setup:
Entities: I have a master entity named StudInformation and a child entity named StudExperience. The child entity uses StudID as a Foreign Key and contains a Years_Duration attribute.
Data Retrieval: I am using an Advanced SQL tool within a Data Action to calculate the SUM of Years_Duration for each student.
SQL Logic: The query uses a LEFT JOIN on StudID and a GROUP BY clause. I’ve also implemented COALESCE to handle potential NULL values in the duration column.
The Issues:
Incorrect Data Mapping: Even though the SQL calculates the total correctly (e.g., 10 + 15 = 25 for a specific ID), the result often populates every single row in the UI table instead of staying linked to the specific student record.
Input Parameter Conflict: In the SQL properties, the StudID parameter is currently mapped to GetStudInformations.List.Current.StudInformation.StudID. I suspect this causes the SQL to fetch only one specific result and repeat it across the entire table.
Data Persistence: There are instances where newly added experience records do not immediately reflect in the SQL output or the UI, even though the data exists in the database.
Question: How can I properly bind the output of my Advanced SQL (DataAction1.List) to a Table Widget so that each row independently displays its own correct calculated total without defaulting to the "Current" record of the list?
Hi @jesu verso ,
I am not able to open your module.
This issue is happening because the SQL input parameter is mapped to GetStudInformations.List.Current.StudID, which means the query runs for the current record and the same value gets repeated for every row in the table.
Instead of filtering by the current record, you should return the aggregated result for all students in the SQL and then bind it properly to the table.
You can modify the SQL,
SELECT SI.StudentID, COALESCE(SUM(SE.Years_Duration),0) As TotalDuration
FROM { StudentInformation} SI
LEFT JOIn {StudentExperience} SE ON SI.StudentId = SE.StudentId
GROUP BY SI.StudentId.
This query will return one row per student with the correct student duration.
Then remove the StudentID input parameter from SQL. For the calculated column, use the value returned from DataAction by matching it with the StudentId.