Note, this expression could significantly impact s...

John_Paul_Armis · 02-22-2021 02:23 AM

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?

Steve

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:

John_Paul_Armis

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.

Steve

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?

John_Paul_Armis

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?

Steve

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:

John_Paul_Armis

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?

Steve

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

How have you indicated that the Province field is required?

John_Paul_Armis

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

Steve

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