Building a quality management app with sharepoint and Power apps

(0) Share

Report

Posted on by Romu2

Hi everyone,

Initially I was struggling building a cascading dropdown (4 levels) from sharepoint lists with power apps.

But after looking at too many videos and tutorials, I thought maybe I was not using MS tools the right way.

The general issue is the following :

I have a Final Customer who only allowed some Specifications to be used on their product based a Raw material of choice. See Excel attached for example.

To the user will select :

1. Final Customer

2. Specifications

3. Raw material

Each level has his selection possibilities limited by the choices done on the previous level.

As the list is not that small, I will need to allow research too.

Initially I was building all my lists on SharePoint with lookup columns to fetch the datas linked from one list to another (I have 6 separated lists in total) and then try to customise the forms with Power apps.

But I get the feeling is it the wrong way of doing it and I end up having multiple unexpected bugs instead.

Question :

If you were to build a back end solution on SharePoint to manage a couple of tables with relationship in them, how will you do it ?

The interface needs to be SharePoint and Power Apps will do the heavy lifting in the background.

Will you start with text fields in the SharePoint and then update all those fields in combo boxes ?

Or is there any other way or resource I could rely on ?

Thanks,

Categories:

Building Power Apps

Specs.xlsx

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

WiZey 3,023 Moderator on at

Like (0)

Report
Hello @Romu2 ,

First, SharePoint is barely a database. You can create lists and store records, however, it simply emulates what a real database like Dataverse is capable of. I'm not saying it's wrong, I'm saying all relations will have to be hard-coded in Power Apps.

Second, I don't think what you're trying to do is unachievable.

I guess the first "mistake" was to use LookUp-type columns. To be frank, they're not well designed and really hard to use. You'd better use either text-type or number-type to store the related record's name or ID.

Then, it depends on how you're populating your comboboxes. To make them inter-dependant, you can write a formula like this:

Combobox1.Items: Filter( List, FC = Self.SearchText ) Combobox2.Items: Filter( List, FC = Combobox1.Selected.FC, Spec = Self.SearchText ) Combobox3.Items: Filter( List, Spec = Combobox2.Selected.Spec, Raw = Self.SearchText )

Combobox1 will fetch all data regardless of their origin, based of course on the user's input in the search field.

Combobox2 will fetch all data based on the selected Final Customer selected from Combobox1, based on the search field.

Same for Combobox3.

Is there anything else you need help with?

Was this reply helpful? Yes No
Romu2 4 on at

Like (0)

Report

Hello @WiZey,

Thank you for your answer, that was spot on.

It would definitely be easier to use a real database but as of today SharePoint is all I get.
In a near future and if we need to scale, there might be a possibility to use Dataverse or even a SQL database but I need to demonstrate the interest for the business first.

I apologise for the delay in my response as I had to re-do the SharePoint as per your recommendations prior testing your solution.
--> It works like a charm for any new line I need to implement but I get an odd behaviour.

When I want to edit a previous line, all the combo boxes are empty and I end up loosing the information that was filled in beforehand.
Is it normal or am I missing something here ?

Thanks!

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like (0)

Report
I'm glad I could resolve your issue.

That's a normal behaviour. The combobox's "Items" property is refreshed each time you change a combobox's value, which end up deselecting their value.

For example: when you select a value in "ComboBox A", "ComboBox B" will be refreshed because it is dependent on the selected item of "ComboBox A".

If you want to have a value selected by default when editing a record, I believe you can set the "DefaultSelectedItems" of the combobox to your record's current value. Keep in mind this property expects a table, so you'll have to write the default value in this format:

Example 1: ["Value"] Example 2: Table({Column:"Value"}) Example 3: Table(Record.Column})

Was this reply helpful? Yes No