Referencing different tables..

[Tiger1]

I have 3 tables.  One table (Table A) is for referencing.  One (Table B)  is an asset list.  The last (Table C) is a work order list.  

My problem:

Table B and Table C have the same column values i need - [Location_code] and [office_code].  However, in one table (Table B)the Location does not have the exact number associated with it as the other table  - Table C.

For example:

In Table B, I have a location - "19-01"

In Table C, I have a location - "19"

My Table A cannot have a key with both values, however they both mean the same thing.    If I make Table A have a key of - "19-01" then I cannot reference to Table C.  If I make Table A have a key of "19" then I cannot reference Table B.

How do I fix this?  I cannot change Table B and Table C.

[Steve]

Table A should have two Ref columns, one to Table B and one to Table C.

[Tiger1]

I didn't realize I could do that.  Let me try that.  thanks

[Tiger1]

I wanted to make sure, I reference Table A.  Table A looks like this:

Table B and Table C reference Table A.  So I need to make Table A reference Table B and Table C?

[Steve]

You've used the term "reference" inconsistently, so now I'm confused.

Please post similar screenshots of tables B and C.

[Tiger1]

Sorry,

Table B:

Table C:

[Steve]

My interpretation is that you want tables B and C to each reference table A, but tables B and C each use a different code to reference locations.

You cannot make both of the existing code columns Ref-type columns that refer to table A.

You can make the Location_Code column of table B a Ref column referring to table A--it will work as-is.

So what to do with table C? Why not use the same location codes as used in table B?

[Tiger1]

I would agree.  However these are the queries my company gave me to use.  It's stupid.  Technically the columns - [Location_Code] and [Office_code] are referring to the same thing.  I am not sure how to make it show the same thing when creating a "dashboard" in my app...

[Steve]

Can you add a column to table A to store the office code?

[Tiger1]

That I can do - I built Table A - so what do i need to do?

[Steve · Accepted solution]

Add that column to table A.

In table C, add a (preferably non-virtual) column named (e.g.) Location_Code with an App formula expression of:

LOOKUP(
  [_THISROW].[Office_Code],
  "Table A",
  "Office_Code",
  "Location_Code"
)

This will populate the new Location_Code column from the Office_Code column value.

[Tiger1]

This worked Perfectly.  Thanks gain Steve!