Alias tables - getting same result for "name" despite use of alias

[ChrisInNH]

I'm not getting desired results for a situation where I need to report 2 separate entity names per row, where name for each comes from the same table. We're using JDE if that helps folks here so I'll illustrate:

Report rows (and summaries, etc) need to have both Customer Name for sales history info and the associated Sales Rep entity assigned to teh sold-to customer. All entity names are stored in a single table: F0101 "Address Book" and the name is stored as "ALPH".

Sales history detail (F42119) can be linked easily enough to F0101 to get Customer name using sold-to AB# key. So for customer name detail show F0101.ALPH where F42119.AN8 = F0101.AN8 for join (I'm not including table-specific column prefixes used in JDE).

But to get Sales Rep name, I first have to join through an intermediate table, F42140 (for anyone who knows JDE, please ignore fact that there's an order-specific Rep relationship as well).

I can join sales history to that sales rep assignment on same sold-to key, AN8. So, F42119.AN8 = F42140.AN8 to find the related sales rep assignment key (but not name as it's not in that table). But then I need to continue "through" F42140 to F0101 to get the Rep's name using a different column in F42140: SLSM. SLSM is still an AB#, just as AN8 is. So, I have SLSM joined to an alias of F0101 (call it F55123) on AN8 to fetch ALPH.

But all I get for results is Sales Rep name repeated for *both* the customer and the sales rep entity for all rows no matter what I do. I do have separate ALPH field outputs chosen from each F0101 (original) and F55123 (alias) in the proper places on the report detail, but no matter.

I'm certain I'm not setting up the alias properly, or the joins, or...but after a good bit of Help review, and experimentation I cannot get the desired results. I feel it's something simple and it's driving me crazy.

Can any of you assist?

Thanks in advance,

Chris

 

[ChrisInNH]

I'm new to this forum - apologies if I have missed an important protocol. Given rapid response from others to other posts here, I'm wondering if I have made an error with my post, its location or some other faux pas?

 

[lbass]

This is hard to follow. Maybe go to database->show SQL query and copy the query and paste it into the thread so we can see exactly what you are doing.

-LB

 

[ChrisInNH]

Thanks very much for the reply. For some reason, "Show SQL Query" is grayed out - not available to me.

Relationship 1: Sold-to customer name from sales history to name, from F0101

Relationship 2: Sales Rep name for the customer, derived first by finding sales rep assigned (in F42140), then separate key in that table to go back to get name in F0101 (since customers and employees are both in that table).

I'm attaching link to an image to show you what relationships look like. Hope that helps. Thanks!

 

[lbass]

I can't see what field you are linking on from F42140 to F55123, but it appears not to be an "address" field. It would help if you provided a crosswalk between these table names and what they mean and if you did the same with the fields, as none of this appears intuitive, and you have only partially done that in previous posts (I believe).

-LB

 

[ChrisInNH]

Thanks again for your reply - sorry for the delay getting back.

Here is a better layout of what I'm trying to do. Sorry if I over-explain:

simplified table descriptions:

F0101 - Address Book (in JDE, contains all entities: customers, employees, vendors, etc)

AN8 - Address # (8N)
ALPH - Entity Alpha Name

F42119 - Sales Detail History

AN8 - Sold To Entity AB#

F42140 - Customer Sales Rep Assignment

AN8 - Customer entity for which Rep relationship is defined
SLSM - Sales Rep AB# (this is also 8N and serves as the assigned Rep's "address book" key. Alternate name for an AB key only because AN8 was already defined in this table)

F55123 - Copy of F0101, aliased as F55123

AN8 - Address # (8N)
ALPH - Entity Alpha Name

To get any given Customer Sold-to Name (F0101.ALPH) on my reporting I simply join Sales Detail History to Address Book:

F0101.ALPH where F42119.AN8 = F0101.AN8. Easy enough.

But to get the Sales Rep's name (also F0101.ALPH since F0101 contains all entities, not just customers), I first have to find the relationship of sold-to entity to assigned rep entity to get the Sales Rep's AB# key which I would need to go back to F0101 to get their alpha name:

F42140.SLSM where F42119.AN8 = F42140.AN8

then use F42140.SLSM (remember - same as AN8) to go back to Address Book for the alpha name. However joining back to original F0101 using F42140.SLSM=F0101.AN8 consistently produced only repeat values of customer name in all my rows.

That's when I assumed based on reading through help that I needed an alias for F0101, one which I could join to from F42140 (mine is the F55123) and try to force a read of ALPH for the sales rep using that join.

But it does not work.

As I have experimented with relationships, join order, etc - I find I get EITHER all Customer Name results regardless of whether report is showing output for F0101.ALPH or F55123.ALPH, OR, I get all Sales Rep names for same.

But what I'm expecting to be able to do is print both the customer sold-to name, and the sales rep name, and use both for sorting, group totals, etc.

This is what I cannot figure my way through.

 

[lbass]

I'm sorry, but even with all this, I don't think I have enough information to tell what's going on--I would need a better understanding of the database. I notice one of the tables has a parent number field, and it may be that you need to make use of that in some way. Not sure. I think you have to observe how the fields in each table relate to each other by placing them in the detail section. Then try linking and adding fields from two tables and again observe behavior. It could also be that you need to add selection criteria on one or more of the tables to get the desired results.

-LB

 

[ChrisInNH]

Thank you for your efforts! I will continue my work - I'm quite certain I should be able to do this, and I'm betting it's something very simple getting in my way.

Chris