Why if I call(lookup) data with a long number the results are always changing?

Bihikmi_ptn
Participant II

Hello,

Sorry if my English bad, im from Indonesia!

we try to lookup data from another sheet but the data displayed is always different, for example:

LOOKUP([NIP Pembimbing Pertama].[Nama Dosen Pembimbing Pertama], “Sheet2”, “Nama”, “NIP”)

Data on Sheet2 NIP is 196009291987031002 but result LOOKUP show 196009291987031000

Why? I just need original value…
it should end in 002 not 000

Solved Solved
0 21 657
1 ACCEPTED SOLUTION

I have just made a test with google sheet. The result is the same as previous test, however, I found that when test using expression assistance, it shows the correct number. as below snapshot. However, in the emulator and google sheets, when the type selected is number, it shown with truncated number (after the 15 digits).

Btw, @Bihikmi_ptn, you may jump-in with questions if you haven’t found the solution to your issue.

View solution in original post

21 REPLIES 21

That is very weird. Just tried it in a test app, I also get the wrong number returned in the same way. However if I change it to a Text type column it returns correctly.

@Marc_Dillon, isn’t that LOOKUP() only for text value?

Not that I’m aware of. I think maybe it returns Lists as Text.

Upps… I got lost by reading the return value syntax:

return-column : The name of the column whose value should be returned as literal text value.

Hi @Heru
My test results with lookup expression with an 18 digit number

It’s different that my test. My test using number over 15 digit always return the rest of the numbers truncated as 000. I was suspecting that it has something to do with excel limits of accuracy 15 digit, but your result shows differently.
Hmmm… something wrong with me.

Hi @Heru,

Thank you. No , I do not think anything wrong with our respective testing.

I note that you are testing with Excel. My test results are with Google sheet as backend. So is there any difference there?

I have just made a test with google sheet. The result is the same as previous test, however, I found that when test using expression assistance, it shows the correct number. as below snapshot. However, in the emulator and google sheets, when the type selected is number, it shown with truncated number (after the 15 digits).

Btw, @Bihikmi_ptn, you may jump-in with questions if you haven’t found the solution to your issue.

You are correct, even I am finding on close obervation that the number truncates on saving. But in case of Google sheet, only the last two digit (18th Digit and sometimes 17th digit ) seem to be truncating.

Even though earlier test results had shown otherwise.

@Heru
Wow!
yes this is the solution I was looking for
Thank sir

@Heru

yes that also happened to me, but after I regenerated it was successful

@Suvrutt_Gurjar
Thank you very much sir…
Glad to be able to enter this forum and meet with you, this reference was successful
and its WORK!

LOOKUP() can be used to get any type value.

I have not tested but my one guess is if the field is retained as a number field ( 18 digits) , then the number is too large to be handled as a number type field by AppSheet number definition?

So is it possible that it truncates some digits?

Probably it needs to be treated as a text and tried with lookup formula?

Thanks Suvrutt.

This is a unique number that must be entered into the template and unfortunately cannot be truncates.

How do you change the text so that results do not change?

Thanks Marc…

How? I have changed it to text, but the result still changes…
help me, it really make me confused…

hru
Participant V

Hi @Bihikmi_ptn, welcome to Community.

Please inform your tables structure, how many tables you have and what are their column names and types and the designated key for each table. Is there any relation between the table?

Thanks Heru,

I tried “SELECT” formula and changed it to TEXT but it didn’t solve the problem…

I still can not see the full table structure, however, if your formula should be something like this:

  1. In case of SELECT():

    ANY(SELECT(Sheet2[NIP], [_THISROW].[NAMA] = [NAMA]))

  2. In case of LOOKUP():

    LOOKUP([_THISROW].[NAMA],“Sheet2”,“NAMA”,“NIP”)

To enable for any of the above formula to work, in your case, you may need to change the format of the sheet to “Plain Text”.

Bihikmi_ptn
Participant II

Thanks Sir…

Your formula is very correct, but the results keep changing …

and I have also changed it in appsheet type to TEXT and in spreadsheet to Plain Text…

Hi @Bihikmi_ptn
Baed on various column name screenprints shared by you, can you please try an expression

LOOKUP([Nama Dosen Pembimbing Pertama], “Sheet2”, “Nama”, “NIP”)

Hi @Heru As a side note of interest, my testing showed that even numbers are returned in LOOKUP() expression, eventhough I agree with you that the syntax suggests it to be a text literal.

Top Labels in this Space