Navigate to Edit Page as a Result of a Drop Down

[DRRR]

HI all - I am new to Papps, and I am having an issue with being able to manipulate a data file ( excel) once I have parsed down my options, via 2 drop Down lists.

I am able to create a local variable from the result, however, I am confused on how to associate that result, with a new screen, so that I can Edit that result's file.

I have looked everywhere for something detailing this, but I am at a loss.

Any help on this is greatly appreciated!

 

[FoDelement]

Is the excel file connected to your app yet?  If so what you are looking for is called "Patch". 

There are limitations of using an excel file in powerapps however. Such as it only supports at max, 2000 line items and a max file size of 1.5 MBs (give or take) 

[Drrickryp]

Hi @DRRR 

Make sure that you have an ID column in your table., this will make future additions and filtering much easier.  It can be a sequential column of whole numbers and they must be unique.  Absent that, in your Edit Record button, create a global variable and navigate to your screen containing the form. 

Set(
     myrecord, Lookup(
                      Table1, Manufacturer=Dropdown1.Selected.Value && 
                       Size = Dropdown2.Selected.Value
                )
); Navigate(Screen2, None)

The formula above assumes your table is named Table1 and the Field names are Manufacturer and Size. Change them to the actual table name and field names if they differ. 

In the Item property of the form, put "myrecord" without the quotes.  The datasource should be the Excel table.

[DRRR]

Thank you Nick! - Set(myrecrd is something I can Search now and I get the premise..

I applying the formula I ran into an error.

I don't quite understand the argument so  I attached a picture of the data Source ( SidingTrimCost) and a picture of the formula

Please let me know your thoughts as I cannot use "Selected.Value" in my formula. I only see "Selected.Text" as an option

I Did rename the Text boxes for description to be accurate to the column names in the excel table

Thank you!!

[Drrickryp]

Hi @DRRR 

Having looked at your Excel table, it has fundamental flaws in it.  Although it would be possible to create a work around, if you are serious about developing using PowerApps, I strongly suggest you review my blog series starting with https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... .  Your table contains a One to many relationship between Material and Items.  If you properly design your app, it will facilitate future modifications that may need to be made.  

[DRRR]

HI @Drrickryp 

I spent quite some time lookin at your blog and trying to decipher how I can streamline my data.  To that end I recreated the tables and have attached them - The only thing I see in the One to Many relationship is that Materials may have many different Sizes, and although it looks as if there is a lot of redundant info, I hope to use that info when I calculate in future steps outside of powerapp. About the only thing I could remove from the data source that was truly redundant was the material name.  That left me with a table of one column.  it seems as though powerapps has insisted that the table have 2 columns, thus it inserted an extra column both times I have tried this.  I have since excepted that and have decided to assign that second column in the materials table with an ID.... not sure if this is necessary though

I guess my big misunderstanding is that by separating the 2 tables, I  haven't quite grasped the benefit, with respect to sorting and combining later.  

That all being said I have my first table looking good, but I am having a tough time stepping into the next screen by referencing the 2nd table relative to the selected item from the first screen.

Your help has been greatly appreciated , as the whole reason I am delving into powerapps, is so I have a better understanding of the structuring of the database.  Your Blog was quite informative and has set me in the right direction, but I am just having a hard time trying to understand the why behind the what...

 thank you

[Drrickryp]

Hi @DRRR 

It looks like you are heading in the right direction. 

1. Instead of letters as the ID, I suggest that you use numbers for your ID.  It will make it easier to assign a new value when you add a product or a new material. 
2. Rename the table containing your Items to something like Products.  Items is a term used in PowerApps formulas and will create confusion.  In my discussions with you I will be referring to a Products table.  Make sure that the ID from the Materials table is included in the Items table.  
3. If you haven't already, make a field called materialsID in the Product table that corresponds to the ID in the Material table.  
4. When naming columns, do not use spaces or special characters.  

Once you have made those changes to your tables, you can let PowerApps build an app for you from your data using the Products table by going to make.powerapps.com and selecting Excel from the create a table from data section. Once you have done that, I will help you to fine tune the app.   I am around today and not busy so I have the time to walk you through it. 

[DRRR]

Aesome - I renamed the tables to your reccomendations

Products and Materials

Both have associated ID's -

ID column for materials table

MaterialID fo Products table 

I have started the App and can't wait to see this work

Strange ot me and my associate that we ultimately removed the Name( boral) from the original table only to break start a new table that associates the  name as a number and then I put that number back into the 2 tables... the number basically represents the name...  I get why this is important on large scale data, when the parent may have multiple data fields for each record, however, when the parent is only one field it seems illogical.... that has been my struggle

[Drrickryp]

Hi @DRRR 

The reason for having a whole number as the ID for a table is so you can automatically increment it by 1 for every new record added.  Numbers are easily indexed so the performance of your database will be greatly enhanced by using an ID that is a number for looking up for filtering a child table based on a parent's ID.  I recommend that you start over by making a new app based on your Products table not on the Materials table.   

If you want a reason for having two tables, think about what you would have to do if one of the Material names changed.  You would have to change it in a bunch of records in your Product table.  Here you only have to change it in one place.  

Lets say you wanted to add contact information to your Materials   You could simply add columns as needed in only one table.  At some point, you may need an Orders table and a Shippers table.  As long as you follow good practices in designing your apps, it will be much easier to do it up front than to have to go back and reconstruct the whole thing.  If you start with a proper foundation and planning you can avoid this outcome:

[DRRR]

I get it Now!  I am A builder / Ex IT guy.... your picture was quite appropriate!

I did recreate the App from Products - and I am still looking to learn how to link the tables together as that will be the basis for my program on multiple levels.  I see that there is a parent child relationship for the drop downs, which I can now see the relationship of ID's but I am unsure how to Associate the Material Id and the Size ID within Powerapp. I am seeing the light as they say but need help for the last leg...

Once I associate the Id's to the materials and ID's, I would like to then go on to view the appropriate data card and then edit...

I get the foundation now - I just wanna get the darn thing framed, so I know what it will look like... The rest Is usually fun and easy.....

I truly appreciate the help!