How to Combine the quantity in inv1.text with inv1.quantity

[shikin]

I have a complicated issues. let me explain in details.

Certain item code, we maintain a standard details like in the picture below and the total qty UDF meaning that how many lines in remarks there:

so that in our sales order item details column, it shows like this:

the 30 unit came from the 30 qty at quantity column here:

here is the formatted search code that our consultant provide to us:

if (SELECT 1 FROM OITM T0 WHERE isnull( T0.[U_TotalUnit] ,0)>0 and  T0.[ItemCode] =$[dln1.itemcode]) =1<br>SELECT <br> replace(replace(<br>CAST(T0.[UserText] AS NVARCHAR(2000))+CHAR(13) + CHAR(10)+CHAR(13) + CHAR(10)+'Total : <'+<br>cast(cast(round((T0.[U_totalunit]*$[dln1.quantity]) ,0) as decimal(12,0)) as nvarchar) +'> UNITS X <'+<br>cast(cast(round(($[dln1.quantity]) ,0) as decimal(12,0)) as nvarchar)+'> SETS'<br>,'<1>' ,concat('< ',round(cast(replace($[$38.11.1],',','') as decimal(12,0)),0),' >')),'<2>',<br>concat('< ',cast(cast(round((2*cast($[dln1.quantity] as decimal(12,0))) ,0) as decimal(12,0)) as nvarchar) ,' >'))<br>FROM OITM T0 WHERE T0.[ItemCode]=$[dln1.itemcode]<br>else<br>select $[dln1.text]

My questions is, I need and AR Invoice crystal reports layout that can combine the qty if the same itemcode appeared. I just manage to combine the qty up there but in itemdetails here, shows only one line itemcode. It should be each of the units line is 240 and the total should be 1920 units x 240sets

Can someone Pleaseeee help me how i want so solve my crystal reports issues? I'm stuck right now.

[Johan_H]

Hi,

Your current solution is particularly fragile. It is not good practise to mix the data that you store with the data that you show.

Any mistake in how the text in the item remarks field is written will break it. Any item for which this syntax does not work, and for which a different syntax is used, and it will break. Any item with a description that contains a key character (for example < or >), and it will break.

I strongly recommend that you create a solution with a User Defined Table to hold all the data, and a formatted search in the item data remarks field, to show it the way it does now. Then change the existing formatted search to also use the UDT, instead of the remarks field, and finally with a UDT, you can use a simple query for your Crystal report.

Regards,

Johan

[shikin]

Hi johan.hakkesteegt , I cannot change it to UDT cause currently this is our practice and there is no issues so far like what you mentioned. Other than your suggestion, is there any ways or code that I can combine this item details ya?

[Johan_H]

Hi,

The solution that I am suggesting would not change the way you work. The Item Master Data would still look the same way as in your first screenshot. The formatted search query provided by your consultant, would still do the same thing, it would simply be more robust, and the issues I mentioned cannot happen. The point is, that even though these issues have not come up yet, they very easily can come up in the future, and it will then be very difficult to find what and where the issue is.

Finally, it would be much much easier to create your report.

You do not even have to move all the data manually. You can write a query (or your consultant can) that will copy all the data from the Item Master Data into the UDT. You can even automate this for when you setup new items, so you can keep doing that the same way you are doing now.

All that said, you can have your consultant create a query that can combine all data from the item details. It would work in a similar way as the one for the formatted search.

Regards,

Johan