Solved: Mismatch between UID and barcode generated based o...

Viktor · 12-16-2020 01:47 AM

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

Steve

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.

View solution in original post

LeventK

@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”
)

Viktor

Yes, certainly. I’ve implemented it now. What difference do you think it’ll make?

LeventK

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

Viktor

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.

LeventK

How that ID is generated? Where the data or that number is coming from?

Viktor

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

LeventK

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

Viktor

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.

Steve

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.

Viktor

@Steve

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?

Steve

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.

Viktor

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

Steve

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]..

Viktor

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()

Steve

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.

MultiTech

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.

Viktor

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:

The user starts a form to create a unit, this generates an ID and a barcode
The user loses connection to the internet
The user selects a new order number ([ION]) which generates a new ID
A new barcode is attempted to be generated but because the internet connection is lost, it cannot
The old barcode image is the one that is used, which now means there is a mismatch

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

MultiTech

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.

Viktor

@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.

Steve

Try:

..., ENCODERURL([UID]), ...

Mismatch between UID and barcode generated based on UID?