Re: Circular Reference likely bug

Joseph_Seddik · 01-21-2022 11:45 PM

Hello,

I have this expression in Suggested Values, working correctly, generating the desired behaviour:

Suggested Values

SELECT(Reservas[resID], AND(
    DATE(NOW()) >= DATE([Fecha Alta]),
    DATE(NOW()) <= DATE([Fecha Baja]),
    IN([Estado], LIST("Activa", "Pendiente")),
    IF("Agencia" = [_THISROW].[siteID].[Tipo],
      IF("Prepago" = [_THISROW].[siteID].[Estado],
        IN([_THISROW].[siteID], [Related Servicios][siteID]),
        TRUE
      ),
      TRUE
    )    
  )
)

In the same column, when I used a very similar expression in Initial Value, I’ve got an error, and the app couldn’t load.

First, here’s the expression:

Initial Value

IF( AND(
  "Prepago" = [siteID].[Estado],
  "Agencia" = [siteID].[Tipo]
  ),
  ANY( SELECT(Reservas[resID], AND(
    DATE(NOW()) >= DATE([Fecha Alta]),
    DATE(NOW()) <= DATE([Fecha Baja])
    "Activa" = [Estado],
    IF("Agencia" = [_THISROW].[siteID].[Tipo],
      IF("Prepago" = [_THISROW].[siteID].[Estado],
        IN([_THISROW].[siteID], [Related Servicios][siteID]),
        FALSE
      ),
      FALSE
    )    
  ))),
  ""
)

This is the error:

Error

I understand the problems of Circular Reference, but this is not one of them. There is no dependencies to create one. Also, the expression gives the correct result on the corresponding rows when tested through the Expression Assistant.

I thought the issue is that the column holding the formula is named “resID”, while there is a [resID] in the expression. However, the second [resID] is different; it is used inside a SELECT() expression and it refers to another table. So they are not the same.

Moreover, I copied the exact same expression in the Initial Value field of another column, named docID. I’ve got the same error but this time the Error Msg told me to check the expression for [docID] which is completely absent from the expression.

Any insight please?

P.S. I don’t have Reset on edit set.

WillowMobileSys

I suspect the issue is with this part:

...
IN([_THISROW].[siteID], [Related Servicios][siteID]),
...

Remember that virtual columns have their own expressions that are, for the most part, hidden from us. They can go through several “levels” of tables to derive the results. If one of those is the table you are operating on OR a table that affect this table, then there is a circular reference.

You can replace the Virtual column usage with the equivalent SELECT() expression to see if that eliminates the circular reference.

One other observation, you might be better served to break these expressions up. For example, the “static” parts of the expressions could be put into a Slice to perform the heavy part of the filtering. You can then share that Slice between the two usages to further filter by BUT you will have a much reduced set of rows to operate on increasing performance in front of the user. The side benefit is that it helps reduce the complexity of the expression.

Joseph_Seddik

Hello John,

Thank you! I will experiment removing the IN() statement and different parts parts of the expression; just to try to find out which part is generating the error. I will tell you the outcome.

FWIW, the column is a normal one, not a VC, but I think you meant [Related Servicios], I will try replacing that also with the equivalent expression. I’ll tell you how it turns out.

I think replacing the “static” part with a Slice is a very good idea. Thank you!!

Actually, the sole purpose of both expressions is to enhance user experience; showing him the valid values beforehand. Dividing the workload between the server and the device using the Slide is a further step towards the same purpose, so despite the additional work I’ll have to do, it is definitely worth doing. I tend to write encompassing expressions, but now, using your idea, I’ll go back and modify relevant already-working expressions throughout my app. So thanks again.

WillowMobileSys

Yes

Yep. And, as I think you know, I was referring to performance when the user opens the Form.

Both expressions deal with the Reservas table and since you are filtering by a date that implies to me that this table will be growing. As it gets big, the current SELECTs will get slower and you have that X2. That slowness will be seen by the user as they open the Form - once for the Initial Value and once for the Dropdown list.

By placing the date filtering in a Slice (and any other filtering you can include), that filtering will be done in the background PRIOR to even thinking about opening the Form. By the time the user gets to open the Form there are a lot less rows to operate on keeping the performance at the time the user opens the Form as snappy as possible.

Joseph_Seddik

Thanks a million

Marc_Dillon

I just submitted a support ticket a few days ago about almost this exact situation. It wasn’t giving me a circular reference error, but it was an IN(), checking against a List Dereference, within a SELECT() (it was returning the correct result in test, but not in the valid_if dropdown in the app). There has not been a solution from support yet.

Try this. Set [Related Servicios][siteID] as a new virtual column in your Reservas Table. Then replace the List Dereference portion of your expression with a reference to the new virtual column. See if that fixes the issue.

Although the circular reference bit is different, so it may be something else. You say it’s not about circular reference, but that’s literally what everyone who has posted a circular reference post also says.

Can you show us what is in the Reservas[resID] column?

WillowMobileSys

Yes! You reminded me. I had a similar issue a couple years ago and solved it the same way.

Joseph_Seddik

Thank you dear Marc!

I will surely try it out and tell you the outcome. Please also share with us updates regarding your ticket with support please!

Sure. resID column is the key column of Reservas table. It has the following attributes:

Show: always
Type: Text, Formatting: Plain Text
Require: always
Key: yes
Editable: ISBLANK([_THIS])
Label: yes
Display Name: "ID Reserva"
Searchable: yes
Initial Value: yes

Initial Value Expression

IF( "agentRes" = LEFT([resTypeID], 8), 
  CONCATENATE(
    RIGHT([resTypeID], 
      LEN([resTypeID]) - FIND("_",  [resTypeID])
    ), ":", [agentResID]
  ), 
  CONCATENATE(
    TEXT(DATE([Fecha Alta]),"yyyy-mm-dd"),
    ":",
    TEXT(TIMENOW(),"HHMMSS"),
    ":",[staffID])
)

Other than that, the fields are empty or left to their respective defaults.

Seems to be a wide-spread syndrome

I meant in my own expression, as it is written at least, I can see none. But I think John is right, we don’t know the internal dealings of AppSheet, which is a pity. So somehow, although the Expression Assistant’s interpreter treated the expression well, and gave correct results in every row, their other run-time interpreter must have produced a circular reference out of nothing.

I think AppSheet team should give app creators some information about how the expressions are interpreted and what underlying environment(s) are being used. It is not like they’d be sharing with us trade secrets. AppSheet is the most obscure platform I’ve seen.

Joseph_Seddik

Dear Marc and John @Marc_Dillon @WillowMobileSys

I finally got the chance to come back to this problem and do some experimentation based on you advice.

I tried replacing the [Related Servicios][siteID] expression as discussed with its SELECT equivalent, but I got the same error. I tried putting it in virtual columns, splitting across more than one column, even putting it in another table, but no matter what I do, I kept getting the same error.

I then removed the whole IN() statement from the expression, so the code now just like this:

IF( AND(
  "Prepago" = [siteID].[Estado],
  "Agencia" = [siteID].[Tipo]
  ),
  ANY( SELECT(Reservas[resID], AND(
    DATE(NOW()) >= DATE([Fecha Alta]),
    DATE(NOW()) <= DATE([Fecha Baja]),
    "Activa" = [Estado]
  ))),
  ""
)

When I did this finally, and after saving, this time, I also got the exact same error 😄

What is circular here? 😄 😄

What do you think? raise a bug ticket? or do you have other ideas? Thank you!

WillowMobileSys

Before declaring it a big, I feel like I don't have a complete understanding yet of the whole picture. We have received bits and pieces over the thread but mostly we have been focused on the expression and not so much on where and how it is used.

After going back and reviewing, this is what I understand using this last expression example, correct me if I am wrong:

You have some column in the Reservas table for which you are attempting to set the Suggested Values expression which selects a single row ID from the Reservas table. Your plan is to provide a list of row ID's for this column to choose from. Do I have that correct?

In typing that out I have realized the issue, I think. There is nothing in the SELECT preventing it from possibly selecting the current row. Consider Edits as well as Adds. You cannot have a REF column potentially select itself - i.e. the current row.

Even if you were to add a condition to omit the current row ID, I think the Expression Assistant will still give you the circular reference. You could try it to see if the parser is that smart!

However, you can create a Slice and use that in the SELECT expression instead. The criteria you currently have will all transfer to Slice conditions nicely. Changing the Suggested Values expression to use the Slice will, I'm pretty sure, side-step the circular reference error in the Assistant BUT you could still create a circular reference in execution as the current row might still be selected, so you would want to make sure it is omitted from the Suggested Values results.

Joseph_Seddik

Thank you John for the reply.

Actually the Expression Assistant accepts the expression AND its test gives the expected results on all rows. It is only after saving that this error occurs. Moreover, the same expression is in Suggested_Values and is not generating any error. Also, the SELECT statement operates on another table not the same one.

To tell you quickly, I have located where the error is caused. It is in the IF statement, more precisely [siteID]. While I still don't see where the circular reference might occur, I believe you are right!

So far we have been focusing on the expression and not the whole picture, and this might not be helping. So let me please draw a schema about the relationships of the tables involved and share it here. Perhaps you'll be able to see what I don't.

Thank you!

Joseph_Seddik

Dear John @WillowMobileSys

Here's the big picture. Thank you!

BTW, forgot to tell you that the problem is already solved with your better more efficient slices solution, so no urgency here. Just trying to understand this behaviour that is weird to me.