Nested drop downs and multiple data sources

[Anonymous]

Hi again,

Firstly thanks to everyone who has helped with earlier questions regarding my 'Petty Cash' app. It is now finished - in that it is now time to pass it on to the non-geeks to see if they can break it 🙂

I'm now looking to extend the app. It currently collects data relating to petty cash transactions for a single place. However the charity I work for has nearly 10 different units, so I'd like to adapt the app to collect data for each of them. I would also like to use the same app to record financial transactions for each of the units residents (each unit has around 4 residents).

So here is the issue:

I would need nested drop down lists so that the user could;

a) Select the name of the unit, and then;

b) Select 'Petty cash' or the name of a resident of the selected unit.

I would then like any data collected in the form to be sent to the relevant Sharepoint List.

It would also be useful to be able to set permissions so that only users from a particular unit could access records for their residents and not other units.

Is this possible?

I feel I would need a Sharepoint list for each unit's petty cash records, and one for each of the residents. Then have some kind of variable set within the form, that the Drop Down menu would change - if so, I've no idea of the formula or syntax to use or where 🙂

[Anonymous · Accepted solution]

Wouldn't a SP list with 2 extra columns do the trick?

like Unit and Resident, this way you will get 1 list which you can filter according to what is selected in the coressponding dropdown.

[Anonymous]

@Anonymous I would be concerned about how large the list would then be. When the current app opens it retrieves the current balance to display - and this is already taking 15 to 20 seconds (a long time when you're staring at a screen). If it was having to trawl through 10 units and 40+ residents to find the correct balance to display, it may take an age.

Also, the managers of each of the units need to be able to go into the list and complete regular audits - without having access to the financial records of other units, without having to filter the results to just list the data they need.

If it's not possible, I will just have to create a template app that can be easily changed for each unit and resident. I just wanted to avoid having so many apps basically doing the same thing.

[Anonymous]

If you do the filtering before you get it to the app. Then the performance to filter is on the Datasource side. which will improve performance

[Anonymous]

@Anonymous Thank you for the reply. How would I achieve the filtering before sending data to the app?

[Anonymous]

If you make a local collection, you can do this while filtering your source.

something like this:

ClearCollect(NameOfLocalCollection,Filter(yoursource, Title = YourFilterValue))

[Anonymous]

Thank you @Anonymous  I will certainly look into this, although I don't fully understand it, it does give me a starting point to explore.

I've actually accepted your earlier suggestion of adding 2 more columns as the answer to the original question 🙂  Having discovered that its not possible to use SubmitForm(variablename) to send the data to different forms depending on the selection of a nested dropdown list, I have decided to use your original suggestion to add a column for the names - which will then be filtered in Sharepoint.

This has led to more questions but I will make a new message as they come up.