I still need to understand how to make this work (if possible)

[Tiger1]

Below are my tables. My problem is that i need [item_status] from Table A and [LatLong] from table B to show up in my map view. The issue has been that Table A [Inventory_item_id] does not equal to Table B [Asset ID]. The ids are different:

For Example:
Table A ID - 145
Table B ID - 00000145

So i needed to some how compare and match those ids first, then show on my map - [LatLong] from Table B and the corresponding Item_status for that Asset ID the [Item_status] from Table A.

[SKETCHwade]

I can’t work out what column you want to use to hold the table B data but an expression that would remove those number is something like:

ABS(Number([Table B ID]))

Number is used because im assuming table b id is not a number column.

[Tiger1]

Where would i put this expression?

Which Table, column and where inside that column (valid_if, Formula, etc…)?

[Bahbus]

You’re still using the old column to Ref Table A. And your expression to get [Item_Status] is also still based on the old column.

[Tiger1]

If i do not reference Table A how do i get the [item_status]?

[Bahbus]

No. You’re missing what I’m saying. You needed the Asset IDs to match so you could reference one table to the other. But all you did was set the new columns as your Key columns. You never changed which column references TableA. As per your screenshot, Asset ID is still showing as a Ref. But it’s never going to link to anything. And the same goes for your attempt to get [Item_Status] because that expression is also still based on [Asset ID] which again can’t Ref TableA.

[Tiger1]

I think I understand what you are saying. However, I cannot use ref type and put a formula in it. So I created a VC in each table so to get them equal in length. I am having trouble now getting the item status from one table to show up
on the other.

Bahbus

Dave Papajcik
Regular

January 10
No. You’re missing what I’m saying. You needed the Asset IDs to match so you could reference one table to the other. But all you did was set the new columns as your Key columns. You never changed which column references TableA.
As per your screenshot, Asset ID is still showing as a Ref. But it’s never going to link to anything. And the same goes for your attempt to get [Item_Status] because that expression is also still based on [Asset ID] which again can’t Ref TableA.

[Bahbus · Accepted solution]

Yes, you can use a Ref type with a formula. Those new VCs that are your Key columns are now what you need to use as Refs. [Assest ID] in TableB is now ONLY used to feed the new VC. Anything that previously used [Asset ID] should now use the new columns. That was the whole point in creating them.

[Tiger1]

Ok. I was thinking i could not do “ref” type if it was a VC. However - i made the changes you said - and it works! You are a LIFESAVER. Thank you so much!

[Bahbus]

You are very welcome.