I'm currently working on a functionality that involves view with huge dataset in Appian and I am planning to replace it with record relationship. I have a situation where I'm using a left join with a table and implementing a self-join with three conditions using the AND operator.
Here's the view that I want to replace.
SELECT `snapFrom`.`id` AS `id`, `snapFrom`.`material` AS `material`, CAST( `snapFrom`.`FCFinalTotal` AS DOUBLE ) AS `FCFinalTotalFrom`, CAST(`snapTo`.`FCFinalTotal` AS DOUBLE) AS `FCFinalTotalTo`, `snapFrom`.`snapshot` AS `snapFrom`, `snapTo`.`snapshot` AS `snapTo`, `snapTo`.`FCFinalTotal` - `snapFrom`.`FCFinalTotal` AS `diffValue`, CONCAT( SUBSTRING_INDEX(`snapFrom`.`calWeek`, '.', -1), '.', SUBSTRING_INDEX(`snapFrom`.`calWeek`, '.', 1) ) AS `calWeek`, `m`.`basecode` AS `baseCode`FROM ( ( `Appian`.`SOE_STAGING_FORECAST_WEEKLY` `snapFrom` LEFT JOIN `Appian`.`SOE_STAGING_FORECAST_WEEKLY` `snapTo` ON ( `snapFrom`.`calWeek` = `snapTo`.`calWeek` AND `snapFrom`.`material` = `snapTo`.`material` AND `snapFrom`.`companyCode` = `snapTo`.`companyCode` ) ) LEFT JOIN `Appian`.`SOE_MaterialMasterData` `m` ON ( `m`.`material` = `snapFrom`.`material` AND `m`.`companyCode` = `snapFrom`.`companyCode` ) )WHERE `m`.`basecode` IS NOT NULL
I have created three records and established the relationship, but i am struggling to add join condition, can someone advice how we can add these join conditions.
Discussion posts and replies are publicly visible
Just pasting the view code and expecting us to reverse engineer its purpose will probably not work.
I suggest to first get a clear understanding of what you want to achieve with that view. Then think about whether you even need that in Appian and how to make best use of related records.
E.g. that cast in line 4 is something you will not need in Appian.
And then
Where are you using this view? In a grid or a chart or some other place? Because it depends on where are you using it. If it is in charts, then you can simply do all the aggregation and grouping right in the chart's configurations. If it is a grid, then you will need to query relatedRecordFields in a!queryRecordType
I am pretty uncertain, what you mean by "
kaushala0003 said:but i am struggling to add join condition
Records have no real joins. you can adress other data through foreign keys perhaps this page will help you more there.https://docs.appian.com/suite/help/23.4/data-modeling-with-appian-records.html
I have a same scenario where I am replacing the existing summary view to Appian Records. From the above message I can understand that after establishing relationship you are not able get the desired record list what you expect. My suggestion would be to have a recheck on relationships established and confirm that proper relationship type is chosen and relationship for all fields in join are made. Once refresh relationship in all three records and try again.
Perhaps we need to get a quick wrap up what the data fabric principles are.1.) get rid of the idea of tables and the desires to join them2.) become familiar how appian is referencing records https://docs.appian.com/suite/help/23.4/reference-records.html
... what ?!? Can you help me understanding what you try to achieve?
To replace databsae view with Appian record relationship, you need to establish relationship with all necessary tables by using the common (foreign) key and then use the custom fields to achieve the view like representation.