topic Match field from 2 tables with different format in App Development

Match field from 2 tables with different format

jorditorras — Thu, 08 Oct 2020 13:08:24 GMT

Hi Gurus,

I have a field that appears in two different tables (same field but different names) and I cannot match them because they appear like:

Table 1:

12345

Table 2:

000000012345

And when I see the fields in a table (in the App), the value of table 1 appears in the left side of the cell, and the one of table 2 appears in the right side.

How can I unify the format in order to match the values? (by the way, table 1 contain more records than table 2, and some of them have a format like 1/ABD-1

Any idea? I've tried to use Num#() in both of them but it dosn't work...

Re: Match field from 2 tables with different format

Vegar — Thu, 08 Oct 2020 13:26:06 GMT

Tricky when you need to preserver both numbers and strings such as '1/ABC-1'.

You could try using an expression like this:

ALT(Num(Num#([YourField])),[YourField])

Re: Match field from 2 tables with different format

jorditorras — Thu, 08 Oct 2020 13:48:03 GMT

It's not working... But in any case, the fields like 1/ABC-1 can be ignored as they are not rellevant.

Thanks for your help!

Re: Match field from 2 tables with different format

jorditorras — Thu, 08 Oct 2020 14:59:54 GMT

Hi, I've found a solution. This is what I've applyed:

,If(IsNum(Replace(LTrim(Replace(USR00,'0',' ')),' ','0')), Replace(LTrim(Replace(USR00,'0',' ')),' ','0'), USR00) as [Measure code]