Data input integrity workaround to vlookup? ...

Mark_Brownlee · 02-12-2018 04:00 PM

Data input integrity workaround to vlookup?

Has anyone figured this out?

We’re building a feature-rich native plant nursery management app.

Everything is going great, but…

We have long lists of customers, order names, plant species, etc and we have many people using the app so data integrity is of critical importance.

We had previously implemented our business system in EXCEL and vlookup was important functionality that currently isn’t fully supported by Appsheet, because although users can add new values, say a new customer, the new customer will not show up in any logic or drop down lists until the table is manually regenerated.

Has anyone figured out how to implement a workaround to this functionality gap?

Is there any way to force a table regeneration from the app?

Or is there some combination of data structure, views, etc to replace auto-populating vlookup functionality?

Here’s the text from Appsheet.

** "Both Google Sheets and Microsoft Excel support a built-in mechanism called data validation rules. Data validation rules allow you to constrain the values that can be entered into a worksheet cell. You can define one or more data validation rules for your worksheet. Typically, you define a separate data validation rule for each column in your worksheet where you need to constrain user entered values.

When you create an AppSheet application from a worksheet having data validation rules, AppSheet will automatically detect these data validation rules and apply them to your AppSheet application. This ensures that data values entered through your AppSheet application conform to the same rules as data values entered directly into your worksheet.

The dropdowns that AppSheet automatically creates from worksheet data validation rules have one significant limitation. AppSheet does not automatically detect changes you make to your worksheet data validation rules. Instead, each time you change a worksheet data validation rule, you must manually regenerate the corresponding AppSheet table. When you do the regenerate, AppSheet will detect the worksheet data validation rule change and update the AppSheet application to match." **

pravse

Hi @Mark_Brownlee, you can definitely do this. See help.appsheet.com - The “Lookup/Dropdown” data pattern

Instead of having an enum type, instead use a Text or Number column with a Valid_If, or use a Ref column. Check this article for the “dynamic” list use case. The “Lookup/Dropdown” data pattern help.appsheet.com

pravse

Also help.appsheet.com - Dropdown from Valid_If Dropdown from Valid_If help.appsheet.com

Grant_Stead

@Mark_Brownlee are you working with @Miranda_Lubarsky I’ve been working with her on another thread that sounds eerily similar to your post… Long story short better data organization is in order…

plus.google.com - Hello everybody. So I have made a slice of a table. The set up looks like t… Hello everybody. So I have made a slice of a table. The set up looks like t… plus.google.com

Mark_Brownlee

Ok, will review these resources and get back.

I don’t know Miranda but maybe we should connect…

hodgson_doug

It appears to me his question is not so much about referenced lists or vlookup functionality, but instead WHEN updates to records occur.

I still find this to be a problem with my application, as we have hundreds of new records each week to work through with a handful of people working simultaneously on them: so unless I use additional logic to assign particular records to certain employees to work on these records, redundant or overwriting work maybe performed in a situation where WE CANNOT HAVE THIS.

I understand the Quick Edit fields update on-the-fly as soon as user leaves the field, but then the app locks foreground while it attempts to update the sheet, and doesn’t release control back to the user until its finished. Is there a way to have this so that other users of my app see changes; (something akin to push notifications?) for appsheet apps, that reflect other users’ changes on MY screen as soon as they’ve begun to make changes IF it pertains to a record on my screen? (either I’m viewing that particular record, or it’s one of the many records in the view on my screen at the time)
I think this is where @Mark_Brownlee’s concern and mine meet: is it possible that AppSheet receives notice that someone else has just begun to change a record that I’m viewing, so that it reflects changes to other users of the app in real time, or as close as possible to it?

Steve

Seems perhaps you haven’t read these:

Nope.

hodgson_doug

What do you mean, “Nope.” Under default settings it clearly locks user out for a short period of time with two screen messages, one right after another in my experience:

“Saving changes (1/1)” then “Syncing the app”.

Those both are a UI lock that interrupt the user’s attention and therefore workflow, and they wait for the app to apply the changes to the database under the default settings. I’ll have a look at the two articles you’ve brought to my attention, thanks.

Steve

That needn’t happen if you enable background sync.

hodgson_doug

That sounds exactly like what I need; however, I don’t see Background Sync as an option under “Offline/Sync”. I’ll keep searching.

hodgson_doug

None of the articles (or their linked articles) above address my use-case: an uninterrupted UI with automatic background Sync instead of manual Sync with Delayed Sync or “Automatic Sync” which produces my aforementioned interruption to the user.

Steve

I used the wrong term, “background”. Delayed sync plus automatic sync is the best you can get. AppSheet has no support for real-time or continuous sync.

hodgson_doug

I’ll try it out. I appreciate your insight. Thank you.

hodgson_doug

Ahh! That works as expetced, thank you.
The combination of ‘Delayed Sync’ and ‘Automatic Updates’ results in the desired behavior: The number of updates yet to complete are stuck to the Sync icon in orange, and automatically disappear once completed.

Only other concern is the time it takes for records created/edited by other users to propagate to the local user…30 minutes is quite the stretch to make others aware: it’s deeply important that we not duplicate work as that’s a quick road to lose clients, not just productivity. If I could get that down to 5 minutes, that would be acceptable. Maybe a workflow could trigger it, but it’s only necessary if local user is viewing a changed record, or user moves to a view that includes a record that’s been updated by other(s).

Steve

Yeah… That’s an inherent weakness of AppSheet.

Note, though, that 30 minutes is if the app is not otherwise pushing updates. Whenever updates are pushed from the app to the server, server-side updates are also pulled. So an active app user should stay pretty current. Confirm with your own testing.

hodgson_doug

I think the 30 minute limitation is by design, to keep costs for everyone down to reasonable. Feels like that may be an opportunity for the devs to increase subscriptions, by giving options to adjust that figure.

But let me clarify: when you say, “if the app is not otherwise pushing updates,” do you mean, when the local user makes an add/update/delete to a row, it also fetches from the database any changes made by other users to the database since last local add/update/delete?
I keep editing this… And does it update just the affected table, or possibly the entire database?

Steve

In my experience, the app pulls down all data updates when it pushes the local user’s changes.