I was wondering if anyone ran into this issue before. One of our products uses a custom field of the multilist type. I ran into a problem when trying to create a custom report using this. The custom property holding multiple list values will look something like 0000000015;000000011, refering to the custom list values from the CustomListValues table. So when I want to have the actual readable names of the values selected I have to do a table merge of my artifact table with CustomListValues, where the Custom List Value ID should be contained in the CUST property. I can use something like ON CustomListValue ID IN CUST property, but the ID is obviously an integer and the multiselect propertys elements are strings. I can cast the integer to a string, but that wont give me the leading zeroes... As far as I know there is no way to do string formatting in E-SQL, but Im not really an SQL wizard so perhaps Im overlooking something here. On the other hand, this seems like a standard enough thing to run into with Spira so I think there should be an easy solution? Any help is appreciated! en-US(C) Copyright 2006-2026 Inflectra Corporation.support@inflectra.com/Computers/Software/Project_Management//Computers/Software/Quality_Assurance/KronoDesksupport@inflectra.comhttp://www.inflectra.com/kronodesk/forums/threads120/Support/Forum/spirateam/reports/2265.aspxthreadId=2265D L (dennis.lardenoye@pie.nl) I was wondering if anyone ran into this issue before. One of our products uses a custom field of the multilist type. I ran into a problem when trying to create a custom report using this. The custom property holding multiple list values will look something like 0000000015;000000011, refering to the custom list values from the CustomListValues table. So when I want to have the actual readable names of the values selected I have to do a table merge of my artifact table with CustomListValues, where the Custom List Value ID should be contained in the CUST property. I can use something like ON CustomListValue ID IN CUST property, but the ID is obviously an integer and the multiselect propertys elements are strings. I can cast the integer to a string, but that wont give me the leading zeroes... As far as I know there is no way to do string formatting in E-SQL, but Im not really an SQL wizard so perhaps Im overlooking something here. On the other hand, this seems like a standard enough thing to run into with Spira so I think there should be an easy solution? Any help is appreciated! Fri, 11 Dec 2020 14:41:02 -05002025-06-23T08:24:05-04:00/Support/Forum/spirateam/reports/2265.aspxmessageId=3898David J (adam.sandman+support@inflectra.com) Hi In general you can cast the strings as integers: https://www.inflectra.com/support/knowledgebase/kb369.aspx https://www.inflectra.com/Support/Forum/spirateam/reports/2024.aspx However that is for a single value custom property. For multi-valued, youd have to first split on a comma. Heres the knowledge base on the string functions: https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#understanding-entity-sql-esql https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/string-canonical-functions Regards David Mon, 14 Dec 2020 19:11:33 -05002020-12-14T19:11:33-05:00/Support/Forum/spirateam/reports/2265.aspx#reply3898messageId=3900D L (dennis.lardenoye@pie.nl) Ok going from this I could do Contains(ArtifactTable_CUST_0X, Concat(0, CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String), ,) OR EndsWith(ArtifactTable_CUST_0X, Concat(0, CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String)) OR StartsWith( ArtifactTable_CUST_0X, CAST(CustomListValueTable.CUSTOM_LIST_VALUE_ID as Edm.String)) It seems like an awkward solution for such a basic thing, but I guess it does the trick... Tue, 15 Dec 2020 10:18:09 -05002020-12-15T10:18:09-05:00/Support/Forum/spirateam/reports/2265.aspx#reply3900messageId=4287David J (adam.sandman+support@inflectra.com) Thanks for the query, we have just published a new KB that uses your join :-) https://www.inflectra.com/Support/KnowledgeBase/KB638.aspx Wed, 09 Mar 2022 03:09:50 -05002022-03-09T03:09:50-05:00/Support/Forum/spirateam/reports/2265.aspx#reply4287