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

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:

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]))

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?

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?

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:
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…

Are the asset numbers the row keys?


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?

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…

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.

1 Like

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

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

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.

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…

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

no - i didnt know what to do there…

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

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

Does software #2 always give you 9 digits?

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.