I have a complex database view that should be converted to an Appian Data Fabric (synced records) model for performance reasons.
I am able to recreate all functionality from the database view with the exception of the string_agg() function. It looks like the only way to access fields from one-to-many relationship record is in the list view/display layer. I need the data to be concatenated and available in the data model (i.e., custom record field) so that this column can be used upstream in a parent related record type.
Is there any way to achieve this functionality in data fabrics?
Discussion posts and replies are publicly visible
I have the feeling that I do not fully understand what you want to achieve. Can you help me?
Stefan, thank you for your assistance.
Allow me to add some additional context regarding my data model:
Main Record: "Form"
Each main record also has a related form type specific record. (e.g., formType1, formType2, etc)
Each of the form type specific records has a "customer" field that needs to be unioned into a single field. One of the form types contains a list of customers that are available in a one-to-many relationship record type.
Based on the formType value in the main record, I need to grab the customer(s) from a related record.
I want to be able to flatten the customers in a custom record field so that the values can be accessed by the Main (parent) level record without the need to put all the form type logic inside the interfaces/expression rule. I want to to calculate which record type to get data from in the custom record field evaluation (a!customFieldMatch) so that this logic doesn't need to be replicated/called in each interface that wants to show this data.
It seems like this is not supported. Am I missing something? It seems like maintaining this logic in the custom record field would be more scalable/less code. I'm able to successfully concatenate the list of customers in a grid view by drilling all the way through the related records, but is there any way to do this upstream in the data model?
Hm ... still not sure ...
But I recently did a multistep aggregation across multiple relationships. I aggregated numeric values into custom record fields on each individual level where each one fetched data from the child records custom record fields. I learned that this works well in contrast to trying to do to many aggregations in one step.
Have you tried Real-time evaluations? In this, you can reference related record fields.
https://docs.appian.com/suite/help/24.3/custom-record-fields.html#prodlink-real-time-evaluations
Yogi, thank you for the response. Yes - I did try real-time evaluation but it doesn't seem to support referencing related record fields when it's one-to-many relationship. (23.2)
Yes, aggregating related record fields from a one-to-many relationship is supported but the only available aggregation functions are "count" and "distinct" count. I need to be able to concatenate the values.
At this time the best approach is just to show the list in each grid - it isn't possible to create a custom record field that concatenates data from a one-to-many relationship.
Just curious - are you only using this for display purposes or are you trying to filter/sort/group by this field?
Thank you, Peter. My main use case is just display purposes but I would prefer if the concatenated field is available in the "formType" data model so that it's available to another related record for the creation of parent level reports/charts/Process HQ without the need to recreate the logic in the display layer of each interface.
Thanks for the context! I phrased it "at this time" because we're always looking for places to improve our behavior for synced records, so understanding more about your use case is helpful for our prioritization of improvements like this!