Solved

Concatenate string and integer fields on generic inquiry

  • 24 June 2023
  • 9 replies
  • 253 views
K
Rank
Userlevel 4
Badge

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? 

icon

Best answer by lauraj46 24 June 2023, 00:50

View original
Kaavya

9 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46 , thank you for your swift response as always! Looks like it worked, I will get back to you after testing few results. Have a great day!

Kaavya
K
Rank
Userlevel 4
Badge

Hi @lauraj46,  thank you for your help so far. The suggestion you provided worked.  I was hoping if you could help me something. I am trying to count count duplicate sales prices on my GI. I only want it to count if a price exists with same inventory id, break qty, price type. How can I achieve that? 

In the below case, the count should say 0 and not 3. 

Example

I am currently counting the concatenated field - Inventory ID, Customer ID, Break Qty

Thank you for your help in advance!
Regards, 

Kaavya

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

In order to use the count function you need to group on the other fields.  See attached example.

Hope this helps!

Laura

 

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46, Thank you for your swift response and sharing the xml file. I think it is still counting all the prices that is active for that item in the system. I actually want it to only count when there is a price for the same item, price type and break qty. I tried on my GI and now everything shows up with a count value of 1. Not sure if I missed something?

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

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 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46 , thank you. I am grouping by fields that have been used for concatenation. But count still shows up as 0. Below is an example I tested:

The result for count should be 2. I have attached the xml file, thank you again!

 

1 Attachment
Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

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

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46, Thank you for your help! This works how we want it to! Have a great day!

 

Regards,

Kaavya

 

Kaavya

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved