First of all, I know this isnโt strictly an Appsheet question as I use an external service as well, but I was hoping someone could maybe identify the issue anyways.
The problem is, that I have a table of โunitsโ in my app. These units have an ID which is a concatenation of their order number and 4 randomly generated numbers. I then also have a barcode for each unit which is an Image column using the following URL:
CONCATENATE(
โhttps://barcode.tec-it.com/barcode.ashx?data=โ,
[UID],
โ&code=&multiplebarcodes=false&translate-esc=false&unit=Fit&dpi=72&imagetype=Png&rotation=0&color=%23000000&bgcolor=%23ffffff&codepage=&qunit=Mm&quiet=0โ
)
In other words, the barcode is simply just the ID of the unit. The weird thing is, sometimes these two, the ID of the unit and the ID on the barcode, doesnโt match, and I really donโt understand why. Both columns are โInitial valueโ formulas, and whenever I test it myself, the barcode always matches the ID of the unit, however, other users of the app have been running into this issue.
For instance, the ID of the unit may be: โMyP7413280โ but the ID on the barcode is โMyP4717685โ.
My guess is that the barcode is generated with an ID, and then somehow, the ID is changed after without the barcode updating correctly, but, I donโt see how this is possible.
Any suggestions and ideas are appreciated.
Best regards
Solved! Go to Solution.
Note that the barcode is regenerated every time the app syncs, so if the stored value is changing, the generated barcode should also be changing.
Thereโs really no way to troubleshoot this without the ability to reproduce the problem. I think you should also consider the possibility that the generated barcode is changing, rather than that the stored ID value is. As the stored ID value is also the rowโs key column value, AppSheet will not allow the value to change. Also consider the possibility someone (or possibly another app?) is modifying the spreadsheet in which the ID values are stored.
@Viktor
Can you try with this and see if it helps to solve the issue
CONCATENATE(
โhttps://barcode.tec-it.com/barcode.ashx?data=โ,
[_THISROW].[UID],
โ&code=&multiplebarcodes=false&translate-esc=false&unit=Fit&dpi=72&imagetype=Png&rotation=0&color=%23000000&bgcolor=%23ffffff&codepage=&qunit=Mm&quiet=0โ
)
Yes, certainly. Iโve implemented it now. What difference do you think itโll make?
Possibly the [UID] value might be coming from another rowโs data inside the CONCATENATE. When viewing the mismatching barcodes and UIDโs, have you checked provided the UID text in the barcode belongs to another data row? With [_THISROW] you refer to the [UID] columnโs value particularly in that row. Itโs a de-ref expression and you can grab some details from the below community post by @MultiTech_Visions
Yeah, I thought about this as well, but it does not seem like the ID in the barcode is coming from another data row.
For instance, if I do a quick search in Excel for the ID โ7685โ (which was an ID in one of the barcodes) nothing comes up, so there are no previous data rows with this ID that it could have gotten it from.
How that ID is generated? Where the data or that number is coming from?
The ID of the units are generated with this app formula:
CONCATENATE([ION], RANDBETWEEN(1000, 9999))
The [ION] column is a Ref to another row in another table
Possibly the problem could be the RANDBETWEEN expression. As the CONCATENATE function refers to this column as a parameter, the expression in the UID column might be regenerating. Try with [_THISROW] de-ref first. If it doesnโt help to get rid of the issue, then we can think about another way - though I have no idea about the solution yet, but Iโll think about it
I suspect this as well. If you change the value in [ION], then the ID regenerates, yes, but that is not an issue as long as the barcode also regenerates each time the ID changes, which it seems to do generally, except for some cases which I canโt figure out the pattern of.
An App formula expression is reevaluated every time the row is updated, so every time you make a change to any column of the row, the ID will get a new value.
To give a column a fixed value the user can see but not modify, put your expression in Initial value (not App formula) and set Editable? to the expression, FALSE
.
My bad, I should have been clearer with my choice of words. The formula is an Initial value formula (as it is the key of the table). The Editable? property was set to true
which I have now changed to false
but I assume that wonโt make a difference since keys cannot be edited?
Correct, keys cannot be edited, nor can they change.
Could you please re-summarize you problem as it currently exists? Wading through the past comments is a bit much.
Of course.
So, I have a table of units. For each of these units an ID is automatically generated with the formula: CONCATENATE([ION], RANDBETWEEN(1000, 9999))
Where [ION] is a Ref to another table (the order they belong to). This is an Initial value formula
Then I have an Image columnn in this table that generates a barcode by sending a URL to an API:
CONCATENATE( "https://barcode.tec-it.com/barcode.ashx?data=", ENCODEURL([_THISROW].[UID]), "&code=&multiplebarcodes=false&translate-esc=false&unit=Fit&dpi=72&imagetype=Png&rotation=0&color=%23000000&bgcolor=%23ffffff&codepage=&qunit=Mm&quiet=0" )
Which simply generates a barcode that has the ID in it. This is also an Initial value formula. Here I have now implemented the advice I got (referring to ENCODEURL() and [_THISROW]) but I donโt think it solves the problem.
The problem is that sometimes the ID in the barcode does not match the ID of the unit and I canโt figure out why. I think the issue is that somehow the ID is changed after the barcode is generated, but I donโt know how that happens.
Unfortunately I canโt figure out the pattern of when this happens either. When I try to test it out, everything works fine, but Iโve had reports of the issue from others
So you think the ID value in the column is changing? Or that the generated barcode is changing to represent a different value?
Should not include [_THISROW].
.
I think the ID value is somehow changing. I donโt see how the generated barcode should be changing to represent a different value.
Iโll remove [_THISROW]
from ENCODEURL()
Note that the barcode is regenerated every time the app syncs, so if the stored value is changing, the generated barcode should also be changing.
Thereโs really no way to troubleshoot this without the ability to reproduce the problem. I think you should also consider the possibility that the generated barcode is changing, rather than that the stored ID value is. As the stored ID value is also the rowโs key column value, AppSheet will not allow the value to change. Also consider the possibility someone (or possibly another app?) is modifying the spreadsheet in which the ID values are stored.
Itโs difficult to isolate out troublemaker records like this, but ultimately you have to find either a single record, or a process, that will allow someone who doesnโt know your app design to simply follow your steps and see the problem themselves.
Iโve tried to create multiple units now without running into this issue.
A scenario I could think of where it could happen, would be something like:
But is this even possible? Iโm thinking, when Appsheet makes the API call to the URL, that if the internet connection is down or the server (that generates barcodes) is down, an error will just be returned or the image will be blank? It sounds odd that it would use the old image (barcode) but that seems to be the only scenario I can think of
In this scenario - after the record was created in step 1, that record is โcompeteโ and contains everything its supposed to.
The record created in Step 4 would be created, but it would lack a barcode.
Unless you created a formula to handle this instance and pull in the previous bar code (when one doesnโt exist in the record) - the record created in step 4 will just not have a barcode - until it gets access to the internet and the record is updated.
@MultiTech_Visions
I see your point, but you may be misunderstanding what I meant (or Iโm misunderstanding what you mean): In step 1 the record hasnโt been โcreatedโ yet, weโre still in the form view here. Only after step 5 does the user press โsaveโ to create the record.
I do not have a formula that pulls in a previous barcode (as these are unique for each unit), the barcode is completely dependent on the ID of the current unit
@Steve
Thank you, I will look into this
edit: Iโve implemented the ENCODEURL() function, but I donโt think the issue is with the generation of barcodes. I think the barcode is generated correctly, corresponding to the ID of the unit at the time. It seems more like the ID changes after the barcode is generated and I canโt figure out why.
Try:
..., ENCODERURL([UID]), ...
See also:
Iโm still open to suggestions on what the issue could be.
I appreciate everyoneโs help and suggestions so far!
Youโre right. I think Iโll have to dig deeper into finding out when this problem occurs and track it down. Iโll consider the fact that the barcode can change as well.
Thank you both for your help, it is greatly appreciated!
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |