In BAQ, How to do a left join to get Memos

Epicor ERP 10
,
1

Just learning BAQs. I need to get a LEFT JOIN working between OrderDtl and Memos for a particular CategoryID of Memo. When I add the Memo table to the query, it does not create a Join and I can’t figure out how to get one. I tried Table Relations, but it does not provide a drop down for the OrderDtl and Memo. I tried adding TABLE Criteria for OrderNum = Key1 and OrderLine = Key2, but it creates a cross join and I don’t get OrderDtl that have no memos.

What am I missing?

Thanks for any suggestions.

2

Answered my own question: Use the little dots around the table boxes to create a relationship. No doubt should I read the manual. It would be SO much easier to just type in a query but since we’re in the cloud that’s not available.

GP

1 Like
3

Nailed it. You’re going to want to put Table Criteria on Memo.RelatedToFile = ‘OrderDtl’, because some Memos may have non-int values in Key1/Key2 that will throw an error when trying to join.

4

If you turn on the preview features, there is an SQL to BAQ feature available that will let you indeed type in a query.

Although, I have yet to test this, and it is in preview mode, so who knows how well it works!

This is available on SaaS, at the current version 2024.2.10.

1 Like
5

it is useful

6

Thanks for the replies. I’ll try the SQL browse. I was told it could not be changed on the browse by our epicor guru so I didn’t try it.

7

Just add a relation and set it the way you want
image