Can you link a table with the relation being a calculated field for one of the tables? I am trying to have a field for every sales rep on an AR Invoice which has their name. The field on the invoice header is a list of reps separated by a ~. I was able to break out the sales rep ID, but not I can’t link those up to the SalesRep table to grab the name since they are all calculated fields?
Any way to do this?
You should be able to use the calculated field to join to the Sales Rep table
What does your BAQ look like?
What is in the display query field?
Try using the Lookup function and paste it into the relationship section.
You can link to calculated fields using a SubQuery.
Put another instance of InvcHead into a SubQuery, and have that SubQuery return your calculated fields, along with the Invoice Number, so you can link it to the rest of your query.
Hi, sorry, when you say link it to the rest of your query what are you referring to?
I’m trying link OrderHed to SalesRep via the calculated fields I made that split up the values returned in OrderHed.SalesRepList. And now I’m stuck. I did create a subquery that returns the two calculated fields and the order number, but how do I can’t see how I use that to link the two aforementioned tables.
Thanks for any help you’re willing to give.
You can split your query up into two SubQueries, one that’s type ‘TopLevel’ and the other as an ‘InnerSubQuery’. Have the InnerSubQuery do the calculation to return your sales rep, then drag that SubQuery into the TopLevel query, and link it to the other table to want to use.
Here’s an example of a subquery:
And here’s how you pull it in and link it to another table using that calculated field:
You can use something like this. Just replace invoice with Order
Type this into your join expression fields
‘~’ + OrderHed.SalesRepList + ‘~’ Like ‘%~’ + SalesRep.SalesRepCode + ‘~%’
Thanks for the replies.
The join solution works somewhat, I’m getting two returns for every order line because we always have to sales people on every order. I have no idea how to parse in the join.
The subquery solution, I cannot get to work.
I’m essentially trying to do this
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[SalesRep].[Name] as [SalesRep_Name]
from Erp.OrderHed as OrderHed
inner join (select
[OrderHed_SalesRepSQ].[Company] as [OrderHed_SalesRepSQ_Company],
[OrderHed_SalesRepSQ].[OrderNum] as [OrderHed_SalesRepSQ_OrderNum],
[OrderHed_SalesRepSQ].[SalesRepList] as [OrderHed_SalesRepSQ_SalesRepList],
([Ice].entry(1, OrderHed.SalesRepList, ‘~’)) as [Calculated_SalesRep1],
([Ice].entry(2, OrderHed.SalesRepList, ‘~’)) as [Calculated_SalesRep2]
from Erp.OrderHed as OrderHed_SalesRepSQ) as SubQuery on
OrderHed.Company = SubQuery.OrderHed_SalesRepSQ_Company
and OrderHed.OrderNum = SubQuery.OrderHed_SalesRepSQ_OrderNum
inner join Erp.SalesRep as SalesRep on
SubQuery.OrderHed_SalesRepSQ_Company = SalesRep.Company
and SubQuery.Calculated_SalesRep1 = SalesRep.SalesRepCode
inner join Erp.SalesRep as SalesRep1 on
SubQuery.OrderHed_SalesRepSQ_Company = SalesRep1.Company
and SubQuery.Calculated_SalesRep2 = SalesRep1.SalesRepCode
And get the
What’s in the Subquery, and is it required?
I think the suggestion by Chris above ( except changing InvcHeas.SalesRepList to OrderHed.SalesRepList) is the easiest. It will have a row for each salesrep.
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
[SalesRep].[Name] as [SalesRep_Name]
from Erp.OrderHed as OrderHed
inner join Erp.SalesRep as SalesRep on
‘~’+OrderHed.SalesRepList+‘~’ like ‘%~’+SalesRep.SalesRepCode+‘~%’
If you are trying to just get the details from the salesrep table on the one row, you could do something like:
This makes the query:
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
[SalesRep].[Name] as [SalesRep_Name],
[SalesRep2].[SalesRepCode] as [SalesRep2_SalesRepCode],
[SalesRep2].[Name] as [SalesRep2_Name]
from Erp.OrderHed as OrderHed
left outer join Erp.SalesRep as SalesRep on
OrderHed.SalesRepList like SalesRep.SalesRepCode+‘~%’
left outer join Erp.SalesRep as SalesRep2 on
OrderHed.SalesRepList like ‘%~’+SalesRep2.SalesRepCode
The problem with this is if you do start using more than two salespeople
The other way to achieve this would be to use the first method in a subquery with a calculated field for row_number() over the OrderNumber and pivot on that although I haven’t tried this method.
