How do I...?

Distinguish text from number

We track houses being built and need to enter lot number to generate lot IDs. sometimes these lot number have letters in them like lot 3A & lot 3B for a duplex. the field is currently a string and not number for this reason.

the issues come to sorting. since it is a string field the houses show up with lot 1, lot 10, lot 100 before lot 2 because of how numbers are treated alphabetically.

i fixed this with the code below:
if({Lot Number}<10,concatenate("000",{Lot Number}),if({Lot Number}<100,concatenate("00",{Lot Number}),if({Lot Number}<1000,concatenate("0",{Lot Number}),{Lot Number})))))

it makes lot 1 lot 0001 so that we dont have the order issue. It only works for 90% of houses though. Every now and then there is an apartment complex where lot numbers are H 1 and it is making them lot 000H. so it is working properly, but it is an imperfect system. I am trying to avoid having 2 fields. A number field for strictly numeric lots and string field for lots with letters. more fields = more confusion and room for error.

any suggestions? i dont know of any "ISNUMBER" functions like in excel available in trackvia

3 votes
Sign in
Sign in with: facebook google
Signed in as (Sign out)
You have left! (?) (thinking…)
Matt Cox shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
Sign in with: facebook google
Signed in as (Sign out)
  • John McGarvey commented  ·   ·  Flag as inappropriate

    I don't know if this would help or not (I haven't tried it), but there is a padding function that you can use to add whatever character you want to the left or right of the value in the field.

    If all your lot numbers will never exceed a certain number of characters, you can set a padding of 5 to preface the lot number with whatever you want.

    If using "0" to pad, Lot 100 becomes 00100. Lot H1 becomes 000H1.

    This looks to be the same thing you are doing with your If statement, so not sure if this helps or not.

    lpad({Field A}, N, "Character for padding")

Feedback and Knowledge Base