i have designed a CDT for Vehicle Maintenance where one of the column is for what are all the last performed services as per vehicle maintenance schedule.
latestScheduledServiceTypes Text 255 Checked the Array checkbox .
So i do expect the column to store the list of values as it is an array?
Can a column in database be used to hold array of values? because in cloud db when i checked no value is getting saved in this column.
Even on the interface, i just displayed the value for this, i dont see any value for that column its just null.
Please help me with the significance of Array checbox in CDT's? and also how to store them into Cloud database.
Discussion posts and replies are publicly visible
latestScheduledServiceTypes is a nested CDT, and is a list of values. Nested CDTs can't be stored in a single column in the table. You need to define another table to store these values and define the foreign key relationship to the parent CDT. Please refer this page for more info. https://docs.appian.com/suite/help/19.2/cdt_design_guidance.html#writing-to-data-store
If your requirement is to store a list of values into single column of the CDT row, this is not possible directly in Appian, You either have to check the field as multiple, which will create a seperate table in the backend for this field.
Or the work around can be to save all the list of values as a single semicolon or a comma seperated string.
Yes there is a backend table which got created automatically.This new table now has id of the main table,array values and their index(3 columns).
May be i can use the id as the foreign key .how can i access those records by the way.
id servicetypes index
1 break pads 0
1 Power steering fluid 1.
Please help by giving syntax.
You shouldn't use the "array" option with a primitive field like text. As you mentioned, this will create a separate table in the database that cannot be directly queried from, which makes it difficult to access the data and can lead to performance problems.
Instead, I'd recommend creating a separate CDT that stores the last management service types. This CDT should contain an id and the name of your service type. Then, in your original vehicle maintenance CDT, add a field for last maintenance service types that uses an array and your new CDT as the type. Once you do this, you will notice that a similar table is created in the database; however, this table can be directly queried from and the data structure will be easier to work with.
One other way to do this is to not use nesting of CDTs and add another field to your newly created CDT for vehicleMaintenanceId. For more information on when to use nested CDTs vs. flat structures, please see the documentation: https://docs.appian.com/suite/help/latest/cdt_design_guidance.html
Thank you so much Peter. That helped. i will try to use CDT as the type and will check the array checkbox
Will get back as soon as i implement this.
i have gone through the CDT design guidance. It says One-to-Many should be a FLAT design?
but in my case for one vehicle many services can be done(1-to-many) and i am now about to design a nested CDT for servicetypes as primitive datatype like TEXT cant take array.
As suggested , for now i have created a nested CDT with id that references master table & serviceTypes (Text field & not an array).
So this table will have no unique key, the data may look like
Id(referencing main table) service types
1 Fuel Change
1 Filter Change
One more help,
In the interface, multiple dropdown will have the value as breakPads,Fuel Change,Filter Change.
Should I take a new rule input for the nested table and what should I give near save selection to and selected Values – cant give directly to nested tables field as it is also a string.
Should I use expression editor to split the multiple dropdown values ? and then store that array into nested cdt?
Please help me understand.
Just to make sure - your nested CDT should look like this:
id - INT (PK, auto-generate)
service - TEXT
Then, your parent CDT should look like this:
latestScheduledServiceTypes - childCDT type (1:M)
Then, you don't need multiple rule inputs, you can save your selections right into the nested CDT for service.
I'm not sure to understand why you said that if we use the "array" option with a primitive field like text, the auto-generate table won't be directly queried. Could you give me more details please ?I've tried to create a CDT with this kind of table (auto-generated by Appian), and this table is listed among the ohers tables. So, I do not see why we could not access the data with a query ?Thank you in advance
The main issue is that you can't directly query the data - for instance, it isn't possible to only query the information from the primitive array without also querying from the original table. This means you end up with weird behavior because you have a table in your database that isn't represented by a corresponding CDT.
It's not only performance though - it's also just that it makes it difficult to work with. For example, it isn't possible to define a filter on the data from the multiple field. However, if you create a separate CDT and table, then you can directly query that table and apply any filters as necessary.
In general I've found that while it may seem easy to set up a primitive array, it makes it a lot harder to actually use the data, and I recommend using other design patterns instead.
Thanks a lot Peter for these explanations. I understand better now.
© 2021 Appian. All rights reserved.