cancel
Showing results for 
Search instead for 
Did you mean: 

Get matrix row data and put it into header field with formatted search ???

Former Member
0 Kudos

Hi All,

I ask your help concerning the following:

On an invoice matrix I want to check all Itemgroup Codes of all items in the rows, if there are some rows' items with ItemGroupcode 101 and some others with anything else, then header field should be Y, otherwise N.

My main problem is: how do I put a matrix row data to a header data with checking all rows in the matrix?

A minor problem is that I can't get the formatted search to work on all rows when the formatted search is assigned to the user field in header.

If I put the formatted search to a row field then the row field is filled with the proper value, but the same query assigned to the user field in header works only on the first row.

What am I doing wrong?

SELECT USEDPROD= CASE T0.ItmsGrpCod WHEN 101 THEN Y ELSE N END FROM .[OITM] T0 WHERE T0.ItemCode = $[$38.1.0]

(SBO 7.6)

Any suggestions are welcome.

Thanks.

Bálint

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Balint,

I believe this is a shortcoming in SAP Business One. You cannot work with formatted searches in the way you are trying to. You cannot retrieve data from all rows and sum them (for example) into a header field. If you work with a matrix, the selected row will be used.

You can only accomplish what you want if the data is already stored in the database then you do a query where you extract the values directly from the database.

Hope it helps,

Adele

Former Member
0 Kudos

Dear Adele,

Thanks for the answer. The major one cannot be solved. OK, I'll try to get a workaround.

However I still do not understand why my query does not work in all selected rows, just in the first row, i.e. if I assign the query to a header field and I'm positioned in the first row it's OK, but when I add a new item to the second row or any of the next rows, the header field is not updated at all.

Why is that so? Do you have any idea?

Bálint