Re: Bug when referencing value from another column

Alejandra_Petro · 02-18-2021 05:36 PM

Hi community,

I have come across a weird behavior on my app, and I have no idea what could be the problem. Maybe I am missing something? Not sure. Hopefully, you can help me figure it out

I have divided my table’s form into two sections “Load Details” and "Delivery Details"

Each section has the following structure:

→ Load Details Section

[PickUp Docket] :

SHOW?: [No_PU_Docket] = “Docket”
INITIAL VALUE: IF(([No_PU_Docket] = “No Docket”), ([ID].[Delivery Docket]), [PickUp Docket])
RESET ON EDIT?: Contains([No_PU_Docket],“No Docket”)

[PU Gross Weight] :

SHOW?: [No_PU_Docket] = “Docket”
INITIAL VALUE: IF(([No_PU_Docket] = “No Docket”), ([ID].[DEL Gross Weight]), [PU Gross Weight])
RESET ON EDIT?: Contains([No_PU_Docket],“No Docket”)

[PU Net Weight] :

SHOW?: [No_PU_Docket] = “Docket”
INITIAL VALUE: IF(([No_PU_Docket] = “No Docket”), ([ID].[DEL Net Weight]), [PU Net Weight])
RESET ON EDIT?: Contains([No_PU_Docket],“No Docket”)

→ Delivery Details Section

[Delivery Docket] :

SHOW?: [No_DEL_Docket] = “Docket”
INITIAL VALUE:IF(([No_DEL_Docket] = “No Docket”), ([ID].[PickUp Docket]), [Delivery Docket])
RESET ON EDIT?: Contains([No_DEL_Docket],“No Docket”)

[DEL Gross Weight] :

SHOW?: [No_PU_Docket] = “Docket”
INITIAL VALUE: IF(([No_DEL_Docket] = “No Docket”), ([ID].[PU Gross Weight]), [DEL Gross Weight])
RESET ON EDIT?: Contains([No_DEL_Docket],“No Docket”)

[DEL Net Weight] :

SHOW?: [No_PU_Docket] = “Docket”
INITIAL VALUE: IF(([No_DE_Docket] = “No Docket”), ([ID].[PU Net Weight]), [DEL Net Weight])
RESET ON EDIT?: Contains([No_DEL_Docket],“No Docket”)

All the columns are giving the expected result except [PU Gross Weight] and [DEL Gross Weight], in only two specific cases:

Case 1:

I press “No Docket” on [ [No_PU_Docket] from the “Load Details Section.”
I complete the columns on the “Delivery Details Section.”

All the columns from the load’s section return the correct value except the [PU Gross Weight]. It only returns a value when I press “No Docket” again even though it is already marked as “No Docket.”

Case 2:

I press “No Docket” on [No_DEL_Docket] from the “Delivery Details Section.”
I complete the fields on the “Load Details Section.”

All the columns from the Delivery section return the correct value except the [DEL Gross Weight]. It only returns a value when I press “No Docket” again even though it is already marked as “No Docket.”

Sorry for the long post. I tried to be as specific as possible so you can picture it properly.

Thanks for your time !

Marc_Dillon

In what way are the values incorrect?

This [ID] column is a ref to another table, are you sure the values are correct there? How is the reference set?

What data types are the [No_PU_Docket] and [No_DEL_Docket] columns, and why do you check a simple equality to “No Docket” in one case, but use CONTAINS() in another?

What exactly do you mean by:

Screenshots typically are much more helpful than text, post a few of the situation.

Alejandra_Petro

Hi @Marc_Dillon,

In what way are the values incorrect?
The expression returns blank.
This [ID] column is a ref to another table, are you sure the values are correct there? How is the reference set?
The [ID] column is part of the same table “Loads”. Values must be correct because the expression works with the other columns except [Gross Weight].
What data types are the [No_PU_Docket] and [No_DEL_Docket] columns, and why do you check a simple equality to “No Docket” in one case, but use CONTAINS() in another?
These columns are of type “Enum” and the values are “Docket” and “No Docket”.

This is how it looks on the form.

Load Details Section:

Delivery Details Section:

So, the dynamic is: if the user has a docket to provide; the user must to tap “Docket” and the form will display the respective fields. If the user does not have a docket, the user must tap “No Docket” and the form will auto-fill the fields with the values from the other section.

Hopefully this video helps:

Steve

Does [ID] point to the row being edited in the form, or to a different row?

Alejandra_Petro

Hi Steve,

It points to the row being edited.

Steve

Then it’s entirely unneeded. Remove the occurrences of [ID]. where it refers to the same row.

Alejandra_Petro

I have removed the [ID] from the expressions, and now I’m getting this error

Do you know how can I fix it? I tried replacing the column that is causing the circular definition with a blank like shown below, but no change.

F(([No_PU_Docket] = “No Docket”), [Delivery Docket], " ")

Steve

Well, the use of [ID]. was definitely the cause of the odd behavior, and it was masking a deeper problem. Unfortunately, your app is much more complex than I’m prepared to troubleshoot. You’ll need to find someone else to dig into this with you.

Marc_Dillon

PU gross weight equals Del gross weight, and Del gross weight equals PU gross weight, essentially. Yes, you have some further conditions on top of that, that you may think won’t create a circular definition situation, but Appsheet still won’t allow it.

I’d suggest trying to explain in plain english what the data collection goal is here.

Alejandra_Petro

The App collects the details for the loads delivered daily; details like load time duration, delivery time duration, load dockets, and delivery dockets. It also calculates whether the load is within the legal mass limits depending on the gross load weight entered. That is why I must always have a value on the [PU Gross weight].

I just thought maybe I could write an expression that uses the delivery weight if there is no Pick-Up docket. I will work on that.

Thanks, everyone for your help and time

Marc_Dillon

So why do the weights have to refer to each other, Isn’t one always going to be entered first, and one entered later on (or if not, just copy the weight from the first)?

Alejandra_Petro

haha I do not know . That is how I thought of it at first, but yeah, there is a more efficient way to achieve it. I will work on it.

Thanks