Sage Report Designer Expression Help

Question

Hi All!

I'm trying to edit our purchase orders so that it works with both types of orders we do. Sometimes we don't add a code for items we're ordering in on a one off basis and we just use the S1 - Special Item code. When using STOCK.SUPPLIER_PART_NUMBER for the expression, we're unable to preview or print any Purchase Orders with a S1 code as this is a POP_ITEM.STOCK_CODE and we just get an error each time when trying to print.

I've tried using the following expression below, but it isn't working as I'd planned and it's just displaying the Stock Code but for any Purchase Orders with a S1 code it gives the error "The report could not be generated. No data was returned for this report":

POP_ITEM.STOCK_CODE Like "S1" ? POP_ITEM.STOCK_CODE : STOCK.SUPPLIER_PART_NUMBER

Any help with this would be greatly appreciated!

Darron Cockram · Accepted Answer

If I've understood correctly I don't think that the problem is down to the expression you are trying to use. Instead I believe the problem will be with the joins. 
 If you edit your layout and go to Report -> Joins you will see a join between the POP_ITEM and STOCK tables. Click on this join line and you will see the conditional join type and join type properties are set to 'inner'. Basically this means that data will only be returned if an exact match is found in both tables. There is no record in the STOCK table with a STOCK_CODE of 'S1' and this is why you get the no data was returned message. The fix is quite simple - just change the conditional join type and join type properties to be ParentOuter and your report should then run for both scenarios. The expression you already have should then work as you expect it to. 
 Hope that helps

Sage 50 Accounts

Sage Report Designer Expression Help

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING