I need an expression to see these items as the sam...

Tiger1 · 01-03-2020 07:38 AM

How can i make my app see these two items as the same? They are the same except the zeros in front of them make my app think they are different:

Steve

Both values appear to be numbers with no non-numeric characters. If that holds for all values in those columns, in your expression, wrap each column reference in NUMBER():

(NUMBER([Asset ID]) = NUMBER([_THISROW].[Asset ID]))

Tiger1

I dont have an expression yet…Where do i put this? Show_if or Valid_if?

Also these are from two different tables - do i put the expression in both tables?

Steve

The subject of your post is, “I need an expression to see these items as the same”. Where do you need to see them as the same?

Tiger1

I needed to be more specific - i am trying to add formatting rules and because the asset ids have different ids (extra zeros in front) the map view and list view do not show same item.

I have two different softwares that my company uses to give information they need. However, the assets (inventory) is the same. So i have two tables here that actually have the same assets in them, but one table has (LatLong) while the other table carries ALL inventory NOT just the ones with GPS devices on them.

So… i am building the app to show a map of the assets along with a list view. In the app the table that doesnt have LatLong carries item status (Ready, In Service and Down). So if you are in my app i need the list view item to show the map view item.

Problem is - those zeros in front of the asset (items) in one software doesnt have those same zeros in the other software. I need the items to show in the list view and the map even though the asset id is different (zeros in front). This way my user can see on the map the “Ready”, “In Service”, and “Down” items.

If i pull up my app now and type in an asset number:
605032
the list view shows - 000605032
Map view shows - 0605032

It does show in both the LIST view and the map.

However, the map does NOT show it “Down” because it doesn’t know those two ids are the same asset because one has more zeros in front of the id…

Steve

Are the asset numbers the row keys?

Tiger1

yes

Steve

Then what you want is not possible as far as I know. You’d have to align all of the key column types, I’d think. Perhaps @Aleksi knows another way?

Tiger1

ok. I wasn’t sure if i could inside the app - compare the two ids with taking off the zeros in the front of them… That seems to be the problem. One system adds a bunch of zeros, while the other doesn’t…

Aleksi

How about if you create a virtual column either with additional zeros or removing them depending on the table? If you set that as a key column, you should have the same key column value in both tables.

Tiger1

Ok, thanks i will try this. Ill let you know…

Bahbus

Different question. If they are supposed to represent the same thing, why is one not simply a Ref to the other?

Tiger1

I did reference one to the other… But in my app - one table is used for the list view (shows all assets) and the other is in my map view (items that have LatLong colum)

The real issue starts with my company using two softwares. One software shows the same asset on a map. The other in a list view. I need the list view assets - that i can flag - “Ready”, “In Service” and “Down” to reflect the map assets - but again the ids are not exaclty the same.

Bahbus

On the column that Refs the other, do you have a Valid If as well?

Tiger1

no - i didnt know what to do there…

Bahbus

Wait, are both tables being populated by the separate outside softwares?

Tiger1

Yes. The Ids match but one software uses zeros the other does not:

Software #1:
Asset id = 179

Software #2:
Asset id = 000000179

I need the two to match so i can show on my map the assets are in a certain status.

The status comes from software #2 (which does not have LatLong)
The LatLong comes from Software #1

Bahbus

Does software #2 always give you 9 digits?

Tiger1

No. That is the other problem. My company has no set procedure or process when entering new assets in. So…

The assets are all different. Some are all numerical. Some have letters.

Steve

Then how do you know when it’s safe to ignore leading zeros?

Tiger1

Well… good question. There are roughly 16,000 assets - but i haven’t come across one that has to keep the zeros in front…

But i don’t know for sure - i didn’t have time to search all 16,000

Bahbus

What about just removing any and all leading zeros from both tables? That should be fairly easy.

Tiger1

I wish. However i am querying this data everyday as my table changes according to the new query results.

So all those zeros would essentially come right back…

Bahbus

Yes, yes, that’s fine. Just create a new VC for each one. SUBSTITUTE([Original Column], "0", "") for the App Formula. Copy the same settings as the original columns for everything else. Set the original columns not to show anymore. And then replace all usage of the original column with the new VCs in any slices and views. Now everything will match. And the Ref should do its linking job.

Bellave_Jayaram

This will substitute 0 inside asset numbers so I think it would be best to find the first non-zero digit and take the right of it.

Bahbus

Ah yeah, you right. I wasn’t thinking clearly about those inside 0s.

Steve

Given that the number of leading zeros are inconsistent, and that some values include non-numeric characters, I would suggest adding leading zeros to enforce a consistent ID length and format. Choose an expected maximum ID length, like: no ID will ever be longer than 15 characters. Then add leading zeros to every ID and trim excess leading zeros to enforce the maximum length:

RIGHT(CONCATENATE("000000000000000", [id]), 15)

Original	Adjusted
605032	000000000605032
000605032	000000000605032
0605032	000000000605032

Tiger1

Thanks Steve. Ill try this method.

Tiger1

Steve,
Do i put this expression on the table that is referencing the Parent table, or the child table or both?

Also - is this a VC or an expression i add to the [id] - like a Valid_if?

Steve

You need to do it wherever you compare asset IDs. Or, create a new column to contain this augmented asset ID and use this new column for comparisons.

Tiger1

Ok. Im still confused of how to do this, sorry. If i compare (use a ref type, correct) then i cannot have a formula…? Correct?

I show the assets on my map - using the child table. So is that the table i need to add this? Also, do i make the VC the Key?

Steve

A comparison is a formula. Unless by “comparison” you mean “sort”, then you are correct: you cannot use a formula to sort a table.

What I recommend is this: in every table with an asset ID, add a VC with an app formula that generates the augmented asset ID as I suggested above. If the original asset ID column was the table’s key column, make the new augmented asset ID VC the key column instead.

Tiger1

oh. ok. I gotcha. I will try this…

Tiger1

Steve,
I created two VC - one for Table A and one for Table B. I still need a reference to Table A from Table B - because I need [Item_Status] to show in my detail view of the assets.

Table A - has the LatLong but not the Item Status
Table B - has the Item status but not the LatLong

This is why i need the asset ids to equal each other (meaning 145 = 0000145) then i also need the item status with that asset to show in the detail of the asset on the map.

With all that being said - I do not see [Item_status] on my map detail.

I also wanted to say - i created a VC (Item Status) in Table A that has formula = [Asset id].[item_status]
Also in Table A i Referenced Table B using [Asset ID]

Steve

If you’re using the new VC as the table’s key column, you’ll need to update any stored Ref values to that table are using the new key values.

Tiger1

I am a little confused of how to do this. Do both tables need virtual key? If not, which one do i put it on? After i have that - how will the other table use the asset ids with different zeros in front of them? I feel like the same problem will be there…

Aleksi

NUMBER(“00000179”) should take leading zeros away.

Tiger1

It still is not working correctly. It shows assets on my map - but it is not coloring (format) nor showing (item_status) - so i think the assets are not referencing correctly.

Table A:

VC (for Table A)

Reference from [Asset ID] —>>>> back to Table B

Table B:

VC (for Table B):

I need an expression to see these items as the same (exclude the 0's in front)