Joining Tables

Question

I am looking to join at least two tables together. As you'll see in the attached photos, I am pulling a table from our billing software and another from our work order software. By looking at these you can see location ID and Location #. These are the same, however the billing shortens this number down to the last four digits of the location number. I would like to join these so that they will communicate. I want to create a web map for those in the field doing shut offs, then when the customer asks how much they owe, the field tech can advise them.

Screenshot (42).png

Screenshot (41).png

Sean_Wray · Answer

If the field is a number field it could be done with series of selects using less than and greater than. If the field is text you could use the "like" parameter. For a text field: Select LocID like '0000*' and calculate those. Then Select LocID like '000*' and calculate those. For a number field: Select where n > 99 and n < 1000. That gets all of the 3 digit numbers and then calculate new field. Select where n >999 and n< 10000. That gets all the 4 digit numbers and then calculate new field. That syntax isn't exactly right, but you get the idea. I know it's a bit clunky, but it will get you there.

ABishop · Answer

Yes, but how would he solve for the ID numbers that are only 3 digits.  Did you review the screenshots?

ABishop · Answer

It looks like the LocationID is truncated down to the last three numbers also, possibly due to a prefix of "0"? You will have to create a unique identifier that matches.  If you want to use these LocationNumber and LocationID fields, you will have to either add "0"s to the prefix of each ID in LocationID or your ill have to truncated LocationNumber field to 3 or 4 numbers depending on the ID.  Also, are they the same field type?  This is important for performing a join.  Once we answer these questions, we can proceed to the next steps.