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
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.
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?
@Marc_Dillon, isn’t that LOOKUP() only for text 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?
Heru:
@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.
Thanks Marc…
How? I have changed it to text, but the result still changes…
help me, it really make me confused…
Thanks Heru,
I tried “SELECT” formula and changed it to TEXT but it didn’t solve the problem…
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?
I still can not see the full table structure, however, if your formula should be something like this:
In case of SELECT():
ANY(SELECT(Sheet2[NIP], [_THISROW].[NAMA] = [NAMA]))
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”.
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.
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.
@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!
@Heru
yes that also happened to me, but after I regenerated it was successful
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.
