I am looking to see if it is possible to create a BAQ that joins the UD101A tabe to the JobHead table using the JobNumber from a tilde separated string in UD101A.Character06. I hope the image provided explains clearly what I hope to do.
Any suggestions would be greatly appreciated.
You can put an expression in the JobHead join field. Use the Entry function.
You can use a CTE for this, but it is painful at best. I’ve posted about this before let me look for it.
I will, thanks…
I ran the CTE BAQ that you had posted @josecgomez (Thanks for the link @hkeric.wci) however I only get one returned row and was expecting 3 in this case. Did I miss something else?
with [SubQuery1] as
(select
[InvcHead].[Company] as [InvcHead_Company],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
(4) as [Calculated_num],
([Ice].entry(1 ,InvcHead.SalesRepList, ‘~’)) as [Calculated_SalesRe]
from Erp.InvcHead as InvcHead
union all
select
[SubQuery1].[InvcHead_Company] as [InvcHead_Company],
[SubQuery1].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
[SubQuery1].[InvcHead_SalesRepList] as [InvcHead_SalesRepList],
(SubQuery1.Calculated_num+1) as [Calculated_num2],
([Ice].entry(SubQuery1.Calculated_num ,SubQuery1.InvcHead_SalesRepList, ‘~’)) as [Calculated_SalesRep]
from SubQuery1 as SubQuery1
where SubQuery1.Calculated_num < 5 and SubQuery1.Calculated_SalesRe <> ‘’)select
[SalesRepbyInvoices].[InvcHead_Company] as [InvcHead_Company],
[SalesRepbyInvoices].[InvcHead_InvoiceNum] as [InvcHead_InvoiceNum],
[SalesRepbyInvoices].[Calculated_SalesRe] as [Calculated_SalesRe],
[SalesRepbyInvoices].[InvcHead_SalesRepList] as [InvcHead_SalesRepList]
from SubQuery1 as SalesRepbyInvoices
where (SalesRepbyInvoices.Calculated_SalesRe <> ‘’)
I got it working and giving the results I expected. Thanks for the tips and suggestions everyone.
