They aren’t.
So I had to do the Calc. Field on Customer, Ship-To, OrderNum, InvoiceNum, and SalesRep.
There is no CashDtl Record.
ok, I think that the union is not going to be the way to make this work. So that means the calc fields are irrelevant. Start with the table(s) that have information for all of the transactions you are looking for. To start with, only include those tables and those fields that you need on those tables. Then when you have all of your transactions showing up, start adding your other informational tables one at a time. This will be where you’re left joins start coming in.
That’s where I started, not know these other, disconnected records were getting added. I had the first part done weeks ago. I was ready to roll the Dashboard out. Then I was shown the Reimbursement and other random bits they tack on to the Excel. That led me here, for a quick answer.
Starting over might be for the best.
Thanks,
Ben
I would agree with starting over.
I think that you have too many filters that are conflicting. And your joins could be run through a single stream of tables instead of around like you showed them. I think you are just trying to do more than you need to.
Do you just need one field from each of those tables? If so I have a cool trick for you. This is nice because it doesn’t puke if it can’t find one.
I read through that but I don’t follow.
My issue is that I’m expecting ShipTo to relate easily to OrderHed, but it doesn’t.
This is where I got all the circular linking you disapprove of.
Okay I’m good with that
which table is the salesman in? (I’m being lazy)
I was pulling it off of InvoiceHead
Is that that goofy ~ delimited list?
Yup.
Here’s what I was doing before:
inner join Erp.SalesRep as SalesRep on
InvcHead.Company = SalesRep.Company
And
Left(SalesRepList,4) = SalesRep.SalesRepCode
that’s the part I think was screwing you up the BAQ. I think we can get it though.
On your join type this in. (ignore the autofill)
3
edit something isn’t right with that… Trying to figure it out.
Yep That 's working!
Thank you!
Make sure you double check that. Maybe we have something different set up, but mine doesn’t seems to be working exactly right.
I got the correct name.
ok, mine isn’t returning any names for regular invoices. But it could be the way we have things entered. The names that come back are for credit memo’s mostly.
I had to use Left(InvcHead.SalesRepList,5). Ours aren’t all the same length.