Note, this expression could significantly impact sync time

AppSheet AppSheet Q&A
, ,
1

Hi everyone may i ask if there’s alternative way to avoid blank data when using LOOKUP and SELECT

I’m with my Form.
With the Field - Province am using formula as Locations[PROVINCE] the locations is from another table.

With the Field - TD Name am using formula as LOOKUP([Province and MunicipalityCity], “Retail directory”, “Province and City”, “TD”) -

When Field Province is blank or no data my TD Name field also dont have data.

What will be the alternative way to avoid the error as: Note, this expression could significantly impact sync time?

2

Like this:

IFS(
  ISNOTBLANK([Province and MunicipalityCity]),
    LOOKUP([_THISROW].[Province and MunicipalityCity], “Retail directory”, “Province and City”, “TD”)
)

It won’t avoid the warning message, but it will avoid the pointless LOOKUP() and the associated performance problems.

Note, too, you were missing [_THISROW]. in your LOOKUP() expression.

See also:

1 Like
3

Good Day Steve thanks for your help.

Want to ask also with the formula in Province field, I indicated formula on valid if as “Locations[PROVINCE]” but still sometimes the data on Province is blank. Province In my Form is the source of all the lookups that i made but sometimes it appears as blank on g.sheets even its required on my Form.

3X_b_1_b1a76b5d7fc363cb0f4d97e4496220bbf1c3d2ce.jpeg
3X_b_1_b1a76b5d7fc363cb0f4d97e4496220bbf1c3d2ce.jpeg782×667 27.3 KB

4

John_Paul_Armisa:

Want to ask also with the formula in Province field, I indicated formula on valid if as “Locations[PROVINCE]” but still sometimes the data on Province is blank.

This can happen if you have two columns of the table next to each other, each of which uses a table-column reference (like Locations[Province]). Is there a column before Province? If so, what is its Valid If expression?

5

Yes i have 3 columns on Table - Locations(Province, City and Barangay) my valid if beside Province is city which is “Locations[CITY]”. Do i have to separate that 3 columns in to 3 tables?

1 Like
6

You’re running into a magic feature called dependent dropdowns, where AppSheet has tried to guess your intent and adjusted the app’s behavior accordingly. Please review the document linked below. If you don’t want this behavior, let us know and I’ll give you the workaround.

See also:

1 Like
7

Hi Steve good day, Thank you so much for your big help. The workaround you are saying is this " Disabling Dependent Dropdowns or is there another way?

8

As I’m looking back at the earlier posts, I realize I may not have understood your problem correctly.

John_Paul_Armisa:

Want to ask also with the formula in Province field, I indicated formula on valid if as “Locations[PROVINCE]” but still sometimes the data on Province is blank. Province In my Form is the source of all the lookups that i made but sometimes it appears as blank on g.sheets even its required on my Form.

How have you indicated that the Province field is required?

1 Like
9

Yes i have set the Province field as required but still sometimes there is a blank cell or data on gsheet.

10

That should not happen. Consider engaging support@appsheet.com so someone there can dig deeper.