Multi-Valued Field Alternatives

[marvelousme]

I just learned that I will have to scrap the Access desktop database that I have been building for 3 years and figure out how to convert all of my beautiful work to an Access Web app. I admit I don't know what I'm doing and have been scouring YouTube and various sites to figure out how it all works.

My desktop database has a table with a very important multi-valued lookup field where end users can select many choices from a combo box in a form. I understand that is no longer an option in the Access Web apps--but I still need a way to:

*limit the type of information entered into this field
*allow users to have the option to select multiple values
*query/locate information for reporting purposes

This table details the employee forms that need to be distributed at the time of hire. The form has a dropdown where the user selects a category. Once the category is selected, the and this "forms" dropdown field is filtered based on the category selected. If I can't store all of the forms in one field, what alternative can I use still make it easy for my end users to enter the necessary information?
:banghead::banghead::banghead::banghead::banghead::banghead::banghead:
I am desperate for alternative ideas on how to make something work. I only have 30 days to learn and convert my database to this web app so I'm open to any ideas that are out there.

Thank you in advance!

 

[isladogs]

Well I've got good & bad news for you:

1. You don't need to learn how to do this in 30 days or ever!
2. Access Web Apps will be fully deprecated next year.
There is no point learning how to do this as the technology will be deleted

Quote by Microsoft:

We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.

See this link: https://blogs.technet.microsoft.com/the_microsoft_access_support_team_blog/2017/03/31/access-web-apps-to-be-retired/

 

[marvelousme]

Hi Ridders,

I believe that is true for Office 365 and SharePoint online. But on-premises SharePoint will still be supported into the 2020's. That is what my company is using with no mention of any changes or upgrades in the near future--which is why I need to figure this out.

 

[isladogs]

I believe you're right but then why move away from a desktop app if its just for use on your work premises?

There are other ways of allowing users to use DESKTOP databases remotely.
For example terminal services.
Much easier to do (though at a price) and no loss of functionality (which would be the case for a web style database

 

[marvelousme]

It's not clear to me as to why this is the selected alternative, but it's what was told to me. I'm not that high up in the decision-making chain...I just do the work. We are upgrading our SharePoint and the new version will not allow us to store as much information in our lists/tables. So the web app is what has been presented.

 

[Minty]

The web app won't change your data storage needs. So that decision makes no sense.

You will find that your ability to retrieve large data sets in web apps is very much more limited than in a desktop database though.

It sounds as if someone hasn't got their facts straight.

 

[marvelousme]

It's not the web app that is affecting our data storage. It's the the SharePoint upgrade. We have an older version currently and we're migrating to 2016. The tables in my desktop application are stored on SharePoint and average about 25k rows.... The new SharePoint will limit data storage to 5k rows per list/table.

Again, I'm not a decision maker in any way. I just perform tasks as they are delegated to me. So any suggestions regarding my original question?

 

[Minty]

Okay - according to this list you can store up to 30 Million items in a list - https://blog.devoworx.net/2016/07/0...ist-library-limits-with-sharepoint-2013-2010/ - Like I said someone isn't being given all the facts correctly.

Back to your issue. In a normalised database structure you would record the formID and the employeeID in a junction table with just those two items (and an ID field probably).

In an access form you can achieve this using a multiselect listbox and some VBA to add the selected items to the junction table.

As Web apps are being deprecated, I'm afraid I have no idea if you can achieve the same in a web form, and if they are only guaranteeing support for 3-4 years I'm pretty sure I would head to a different solution. Just my 2 pence worth.

 

[jdraw]

Your situation doesn't seem realistic.

I will have to scrap the Access desktop database that I have been building for 3 years

You've been building for 3 years--does that mean trial and error? How did you decide upon multivalued fields? Do you have documented requirements; have you done analysis and design; has this database been tested and accepted? Do you have materials that could be given to a contracted developer for building on some new platform?

I only have 30 days to learn and convert my database to this web app so I'm open to any ideas that are out there.

It sounds like you are the only one involved in the database, and that you aren't involved or privy to the decisions regarding hardware and software. Very strange set up indeed.

As ridders said the Access web apps are being phased out, so that isn't a viable option.

You might consider hiring a contracted developer (analysis/design, database??) depending on documentation and funding.

30 days to convert anything is an obstacle; and very big obstacle if your situation is as dire as you say.

 

[isladogs]

You say you aren't involved in decision making.
It also sounds like whoever is involved doesn't really know or understand the facts.

Having heard several of us making very similar points, I suggest the following:

Write a reasoned but informed argument about the issues related to migrating to a web database. Present it to your bosses & ask them to review it before proceeding.

If the bosses aren't totally blinkered, they may well thank you for your input and reconsider.

If they still insist on going ahead, then you've covered your back in term of the outcome being, almost certainly a product that is worse than what they have now.

Good luck

 

[Minty]

And further to add weight to Ridders and jDraws comments, 30 Days isn't enough to do anything from scratch in a development environment you don't know anything about.

It almost certainly wouldn't be long enough for an experienced developer in all honesty, not allowing for any planning and consultation periods as well.