Hello, I am trying to concatenate string and integer fields from ARSalesPrice Table - InventoryID, CustPriceClassID, PriceType, CustomerID, BreakQty.
The purpose of this GI is to count if we have duplicate prices at the same break qty. But currently, it does not let me concatenate string and integer fields together. Could you please advise how that is achievable?
Best answer by lauraj46
Hi
You can convert fields that are not strings to strings using the CStr function - something like this:
=CStr([ARSalesPrice.InventoryID]) + ‘ ‘ + CStr([ARSalesPrice.CustPriceClassID])
Beware that this formula will return the numeric codes for these fields. When you display the InventoryID field by itself, the schema allows it to display the human readable code. But in a formula it will return the numeric identifier.
If you want the InventoryCD field then you will probably need to add a join to the ItemInventory table and use [ItemInventory.InventoryCD] instead. In that case the field would already be a string so you might not need the type conversion.
Hope this helps!
Laura
+8Hi
You can convert fields that are not strings to strings using the CStr function - something like this:
=CStr([ARSalesPrice.InventoryID]) + ‘ ‘ + CStr([ARSalesPrice.CustPriceClassID])
Beware that this formula will return the numeric codes for these fields. When you display the InventoryID field by itself, the schema allows it to display the human readable code. But in a formula it will return the numeric identifier.
If you want the InventoryCD field then you will probably need to add a join to the ItemInventory table and use [ItemInventory.InventoryCD] instead. In that case the field would already be a string so you might not need the type conversion.
Hope this helps!
Laura
Hi
Hi
In the below case, the count should say 0 and not 3.
I am currently counting the concatenated field - Inventory ID, Customer ID, Break Qty
Thank you for your help in advance!
Regards,
Kaavya
+8Hi
In order to use the count function you need to group on the other fields. See attached example.
Hope this helps!
Laura
Hi
+8Hi
In order for the count to work correctly, all of the fields in your results should be either grouped or counted. If it still doesn't work, please attach the XML for your query.
Laura
Hi
The result for count should be 2. I have attached the xml file, thank you again!
+8Hi
The count is returning 0 because one or more of the fields in the concatenated string formula is null, resulting in a null string. The aggregate COUNT function counts the number of non-null values.
Rather than building a concatenated string, why not just show each field separately and use the <count> field to count the number of rows? For this to work you should group on all of the same fields that are displayed in your results table. A revised XML file is attached.
Laura
Hi
Regards,
Kaavya
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
