PowerApps SQL LookUps and Relationships
HTML-код
- Опубликовано: 10 июн 2024
- In this video, you will learn about setting and using PowerApps SQL Lookups and Relationships. If you have a proper key structure in SQL and you need to learn how to make it work in PowerApps then this is the video for you.
Getting started with SQL and PowerApps - • Azure SQL Database tut...
PowerApps and SharePoint Lookups - • PowerApps SharePoint L...
PowerApps Consulting - www.PowerApps911.com 
Наука
Loved your video, was struggling with exactly this. Was impressed that you had this up 3 years back. Thank you.
Thank you very much, Shane. In a short video, you summarized all important issues about using joined tables. Great work!!
You're very welcome!
Awesome man! these little things are sooo important and not found anywhere else! Thanks for having a video on this!
Hi Shane, excellent video - got an example working myself, so really chuffed with that - thanks.
Fantastic! This is an old video. I need to revisit some of these topics. :)
Great, Really Awesome! Thanks Shane!
Shane, this caused me many weeks of headaches. Thanks for this video!
Glad to help. 😀
Thanks for all your work Shane! I subscribed
Awesome. Thanks!
Very clear description of adding SQL to PowerApp, good for newbies.
Thank you!
This is just what I needed. Many thanks.
You are welcome
That's what I'm looking for! Thanks Shane!
Glad to help. 😀
exactly what I was looking for. thank you sir
Glad to help.
Great video Shane and thanks again. How would you do the same thing based on a combo box and how would you encorporate the search function on the lookup?
This was super helpful. Thank you so much.
Glad to help. 😀
thank you so much ... that was exactly what I was looking for ...
Awesomeness
Thank you for the video. Looking forword for series of videos with SQL+PowerApps!!!
Me too. 😀
You are the best! It's been so meny times when you saved hours for me :)
Happy to help. Have a great day. 🐶
exactly what I was looking for. thks
Awesome!
Increible muchas gracias
De nada!
Thank you so much!
Happy to help. Have a great day. 🐶
Great video. Any advice on doing this with many to many relationships using a SQL link table please?
Thanks for this video. Very helpful. I am just struggling with your new "blank" record. I do not know if this is useful in a DB environment. Sometimes you have in your master table no key maintained for a record in the detail table. Then you need to show a blank entry in the combobox. No idea how to do.
Shane #MayHisNameBeBlessed Young, thanks a lot! I had got something working but it was a Filter, within a Distinct, within a Filter again, Within a distinct again, withing a Concat for a label, it worked but looked messy and I was sure there was a cleaner way ...... and after watching the video I used a Lookup function, within a lookup function and got my result in one line!!!!!
I suggest doing a small video of doing a lookup within a lookup (and maybe within a lookup again) to show how deep you can go in a DB to get a name for an ID as depending on how normalised a DB is. If you have to go 2, 3 or 4 tables deep to get that name for an ID, you will have to go 2,3 or 4 nested lookups deep to do that. A video showing this in detail would help many I think. Thanks again!
Glad you got it. 😀
@@ShanesCows Please consider doing a video as I suggested about doing nested lookups to get the correct data in a normalised DB! It would help me and I think many others. Anyway, thanks for all your videos, keep them coming.
Thanks Shane, great video..1 question though, is there a reason why you aren't using combobox instead of the dropdowns? or it's just a preference
Mostly preference. Combo boxes are annoying 😀 for me the maker so i rarely use them.
Shane, love the video, this is helping me greatly in my project. I am having an issue where, after following your instructions exactly, I'm getting a "Expected Record Value" error in my card Update field. The field will populate and anticipate that I'm looking for the Id to be returned, but it shows an error on it.
Maybe a different column type that is complex? Like a choice field? This video might help you understand more of what is going on ruclips.net/video/gsk14D-CYRE/видео.html
Super helpful video. Thank you very much. In fact, this is my 3rd video learning from you. All very helpful. Now knowing the basic concept of it, I am going to see if same can be applied to Excel . We are even poorer, can only use Excel as data source. Again, thank you very much.
Good luck. 🍀👍 😎
Thank you!!
Welcome. 😻
Thanks Shane, will you be doing a video on CDS with entities relationship on Canvas PowerApps too? Look forward to seeing that.
I need to for sure.
@@ShanesCows In the meantime, would you know and be able to send me a link, if there is any existing Microsoft document that illustrates how a Canvas Form can write data to two or more CDS entities with relationship?
Hi Shane, first of all would like to thank you as having seen your videos I had a real solid base to go and build a credit approval app. Just wanted to know if you might have a solution to a problem I am running into. My app is build using two different Sharepoint lists to save the data. But constantly having issues with the internet/server connection and the app not writing back some records to the SP list. So what happens is that users go and process a whole load of credit requests, email notifications are sent, some records are written back to SP lists successfully, but some of the records are not written to SP lists.
You could have the email and other actions added to on success for the form. That way if the form fails to submit then the other things don’t happen.
Hi Shane, thank you very much for this video.
I am wondering if it is possible to create a LookUps between Sharepoint List and Excel?
I don’t see why not. Data sources shouldn’t matter.
Hi Shane! Thank you for this video. How can I execute a stored procedure through PowerApps? I have a collection that was built by the user selecting a set of checkboxes. This collection is a list of IDs. I'd like to populate a SQL Server table with data that is gathered around that set of IDs so that whatever edits the user makes in subsequent screens will be done in this table. My plan is to pass the list of IDs from PowerApps to the stored procedure in my database. The stored procedure will then populate the table. Thanks in advance.
Brian covers it in this blog post. powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/
Thnxs Shane, this works great. Now I tried to use a singleline combobox instead of the dropdown. The update is working but the default value stays empty. (Testing the default formule in a text label does work, so the formule is good) Any tips about replacing a dropdown to a combobox?
I have a whole video on ComboBox.ruclips.net/video/pjs0ZsnJZXo/видео.html Remember you have to set DefaultSelectedItems not Default.
Nice video Shane.
A couple of things that maybe worth mentioning in passing (though too complex for a foundation) or covering in a later video... unless I get there first ;)
* You can use a View in SQL to display the customer name in the gallery (though not for the Edit/New screens).
* I am cheap and my Azure SQL Databases are on the lowest performance tiers, so for information that is not updated frequently (like the customers) that is used in LookUps, I find it useful to have that data in a collection and have the LookUp check the collection instead of the database (as I have found LookUp is slow especially on cheaper databases). This way you can also add the 'Default' option (blank or 'Select Customer') for a new record to the customer collection within the app (using patch) and not risk anyone deleting the 'Default Customer' record from the SQL customer table because they do not appreciate the significance of the record to the app and can't see any related data within the database.
Thanks Paul. Yeah, there is so much to cover with SQL. It is hard to know where to start. :)
Hi Shane, can you make a video on editing powerapp templates? For example using the Budget Tracker, I want to add a date picker for each expense, and an option to input a negative value because I'd like to design an app that can buy and sell.
Hi - Probably not. I am not a big fan of editing those apps. I prefer that you look at them and get ideas from them but then you build your own app with the functionality you need. It is more work in the beginning but this way you know what you have instead of a crazy template you barely understand.
Thanks for the response Shane. I guess you're right, the best way to learn is to make one from A to Z.
Hi Shane, first of all thank you. Still one part is missing, when you add a new Project how the db in the backend is going to write the proper FK CustomerID in the table Project considering the the ID in the table Customers is autogenerated. Thank you if you can clarify this or let me know where I can find information about it.
Great!
Thank you!
Great video! I'm doing a simple change by referencing a table from SQL server in a dropdown instead of hardcoding the values (as we used to have). I simply added the new table in data source, this table only has 2 columns (id and value), I added under Items: '[dbo].[table]'.value, and Default: ThisItem.value but nothing shows up. Is there something I'm missing where it needs to be added somewhere else? :/
Items is dbo.table and the use fields to your right to set which field to show.
Hi Shane. You are a legend. Been looking at videos for the past couple of days. I know this video is old and I had a comment/question that I just figured I would fire off and also give you some kudos.
In the drop down. Do you need to have a record ID 3 in the sql table that has a blank value. Cant you just set it to ""? The blank option now shows up the dropdown which is not a valid option i presume as a user should not be able to enter a blank customer as it screws up your realtion between tables. Also maybe adding a check on save to checks that all values such as customer name, project and status can not be blank. I had a sql where similar values did not allow null by my design and i press save in app I made and figured it would be saved, but it did not as my table did not allow null. I of course knew that but making sure that a user cant enter invalid data seemed like something i could and should handle on a save button. There is probably many ways to have a good pattern for this mechanics whise and most likely something that you cover in the next 60 or so videos that you have posted since this video.
I think i would have added an option called "not selected" or "not sure" or something that sort of lets my user add say I dont know the customer, allthough i most likely would have prevented not selecting it in the first place.
You can set the displaymode of the button to disabled if the Dropdown IsBlank. Also Dropdowns now have a setting called AllowEmpty you can use. 😀
@@ShanesCows Thanks for the reply. Keep up the great work
Thank you Shane. Can you share how to connect to an on premise sql database using a gateway in Powerapps?
This video will get you started. Powerapps on premise data gateway ruclips.net/video/uqx_h4Ym8ng/видео.html
Awesome video Shane! Valuable information but I still missing something. I have 2 SharePoint lists so I’m using 2 different forms. On the parent form I created a button that navigates to child form and what I would like is to edit information from a child form that correlate to parent form. Can you help? Thank you
Look at my series on Repeating tables like infopath. I show this concept differently but should help
Hi @Shane thank you for the great video. One question how did you deal with adding a record to the table with the auto incrementing primary Id field. When I create my form is requires that field?
Did you the column to be identity and auto increment in sql?
@@ShanesCows Yes but after creating the connection. I have managed to patch now after recreating the connection.
Great video, I really appreciate this type of content. Is there another way to return the data faster? LookUP usually takes a few milliseconds. Thanks.
Lookup is about as fast as it gets.
Just wondering if you recommend using the lookup function for the one item as you did for customername or just setting it to a variable on the previous screen when you select the item in the gallery?
Variables are always better. 😀
Thank you so much for your video.Really good!!. I have a general question. For excel the max visible records within a collection is 2000rows, is it the same also in case I'll use a SQL database? PLEASE let me know because my company is considering to buy a licence for the connection, THANK YOU AGAIN Shane :-)
Yes, this video will help you understand the 2000 items limit better ruclips.net/video/UaBmVUQx_Fk/видео.html
Hi Shane,
Great video, just one question: Will we be able to remove the 2000 data row limit if we go with the Azure SQL route instead of SharePoint? Or will those be the same?
2000 row limit for what? Most of the data functions are delegated to SQL so no limits apply. 😀
Thanks for the quick response, Shane. The 2000 data row limit is currently applicable to SharePoint. Thanks for pointing it out!
Hi Shane,
I agree that SQL is probably the best datasource for real robust productions apps. One of the main advantage is how it deals with delegation, which is one of the most important concern when building apps.
In this video you are using the ShowColumns function which is not delegable. How would you deal with that ?
You could put the data into a collection then use showcolumns against the collection?
Still delegation issues. As far I know, not possible to collect more than 500 (up to 2000 under experimental feature) items from a data source. Beside, I agree that's kinda inappropriate to use a drop-down list with more than 500 items, so your video's explanation are more than good.
For large amount of data lookup Combobox would be more appropriate but search not delegable neither. I need to try a combo textInput+gallery, like Todd Baginski o365 user picker.
Great video, so my question is, if I have two tables, say in Salesforce, that I effectively want to query with a where clause. Lets say both tables in Salesforce have 10,000 rows or more, and if those tables are pulled into PowerApps, doesn't only pull the first 500 or 2000? So similar to your example, if one table is contact(we have 10,000 contacts), and had the contact name and customer ID, and the other object/table in Salesforce is a custom object that tracks visits (we have 100,000 visits) to a restaurant, and it records a visit, date, time and customer ID, but not name. Now in my loyalty PowerApp, I want to show a customer their visits over the last 6 months.. This seems similar to your example in this video, but how does the solution in PowerApps work when you have that number of records in the originating database? In addition to this video, can you point to any other resources that would be useful in working with SQL sources and data within PowerApps? Thanks.
top trick!
Thanks :)
Hey Shane Just watched the above great info.
What I want to do is create a gallery based on 2 tables data. The tables are related on an sql.
What I need is to filter the gallery using a main and secondary filter. Is that possible, I have tried but can' get it to work.
Cheers
Steve
Yea Steve. Maybe check out my video on cascading Dropdowns. It may help see how to connect a and b. If not the second video in my Infopath series has two related galleries
Hi @Shane
Thanks for all because Thanks to you I can learn a lot faster Power Apps.
I have a little question. When I do a Lookup on a SQL table and I view it from the development interface, I see the data.
However when I publish my application so that other people saw it, the application does not seem to have access to SQL tables in lookup (and this even for my admin user)
I have this error message: The requested operation is not valid. Answer by server: Sorry ... We did not find the "Value" column of the table. inner exception: Sorry ... We did not find the "Value" column of the table. clientRequestId: *****
You would have an idea of why and how?
Thanks for all
No. What you are trying should work. What does your code look like?
Hi Shane, Thank you very much for the video.
I have a list consists of 10000+ records, I can't use dropdown. Could you please advice is there anyway to update "Data Card Value (Text)" to "Id" ?
Same here, how could we deal with normal Datacard value instead of dropdown. thanks.
Shane, first of all thanks for your videos. I'm just starting out and they are invaluable. However I cannot get the equivalent relationship Lookup working for two SharePoint lists Players and Teams with TeamID as primary/foreign key. Everything I tried redlines with an error as I use your syntax on your Lookup statement. Do you have a SharePoint list relationship video, with proper ID's not just names? Or the equivalent syntax? I will move to CDS, SQL for my personal stuff but my colleagues at work still use SP lists so would be really useful to do it here as well. Thanks. Ewan, London, UK
Love the avatar. 😻 Try this video. ruclips.net/video/xgznk4XlPCo/видео.html
Shane, thanks. I actually cracked it last night and now have a label with a name by lookup of ThisItem.TeamID.Value (I think with .Value) but now its off to your SQL relationship video again to turn that into a drop down as a selected item. Then your repeating tables video will be after that. Sounds very interesting. Yes my avatar is called Pundit Dog. He predicts soccer results using Power BI :)
Hello Shane,
Many Thanks for your tutorial. However for me below format worked for lookup table.
LookUp('[dbo].[ENUM_ScriptStatus]', ThisItem.ScriptStatusID = ScriptStatusID, Description)
where scriptID is the foreign key from the Build table and I want to get the description through lookup in Enum_scriptstatus table. (ThisItem was missing in the video)
Regards,
Nil
Thanks for sharing
Hi Shane! Can we use lookup when we are using SQL view and tring to use editfor in the underlying tables
I don't see why that wouldn't work. I use LookUp all of the time with views. THen when you try to edit the source table you just need a way to connect the dots.
Hi Shane, thanks for the video.. I have app that uses the barcode scanner to read a barcode which in essence is a "ItemID" from a SQL Table, the value is then being passed to a global variable. I then want to lookup the actual Description from the same sql table based on the ItemID and populate this into a text box.. Barcode scanner is reading the data and passing it to the variable from what i can see, however the lookup function i am not having much luck. My formula for the lookup is
LookUp(StockItem,ItemID=Value(ScannedValue),Name)
Stockitem -being the sql table which is a datasource
ScannedValue = Global Variable
Name = Being the Description that i want to pull.
Any help would be appreciated. 🙂
Put Value(ScannedValue) in a Label. Does it show a number? If you edit your LookUP and put in a valid ItemID hard coded doesn that work? These would be my troubleshooting steps.
Hi Shane, I've built my app connected to an SQL database. I can display the data and also edit it. However, I can't add any new items. I do have a primary key and the add button on top as you mention (+). I still get the error that says "There was a problem saving your changes. The data source may be invalid." Any idea on what could be happening? Thanks a lot!
Required columns or trying to add the wrong data type. Text to a number field type of thing.
Hi Shane, Nice video. i've been looking the way how to join two tables in powerapps, my datasource: sharepoint list, but the only way i found is using the function "Addcolumns" and Lookups, but addcolumn is not delegable so it can not work with huge list of records. any idea how to join tables in a delegable way? i want to set a datatsource(items) to a gallery using data from two tables.
I don't have a good answer for you here. :( Sounds like you had all of the right thoughts so far.
I did the same thing. But in my detailed screen it shows... The first value from drop-down always. It doesn't change after changing in edit form. Everything works in database and tables though. How to fix that
Set the Default property
Hi Shane - thanks for videos!
Quick question: how would submit a datatable from powerapps into SQL?
What do you mean data table? What is the data source?
@@ShanesCows on powerapps>menu insert>Data table.
Then you connect that data table with sql.
What I am trying to do is to build a editable table to write back to sql without the back and forth in having multiple screens to do so.
Any help is appreciated - thanks!
Don’t use data table. You want to use a gallery. This video isn’t exact but will get you thinking about editable galleries. PowerApps Repeating Tables like InfoPath Part 1 - Enter the data ruclips.net/video/xgznk4XlPCo/видео.html
@@ShanesCows I already watched that video. It is close (and cool btw) but I am interested in submit updates in bulk (multiple items at the same time ) instead of one by one.
Thanks for replying!
ForAll is the key to bulk updates usually.
Strange, if in the same situation I use for the items property of the dropdown '[dbo].[ras]_1'.naam I get all the items correct in the dropdown but if I use ShowColumns('[dbo].[ras]_1';"naam";"ras_id") I get no errormessages whtasover so all table and field names are correct (; or , differs because of location) but I get zero items.
If I just fill in the tablename in the items property all goes well and I can also select the appropriate field in the update property of the datacard without errors. Simplified over time perhaps..
Interesting. Glad it is working for you. 😀 That is all that matters.
Hi Shane, how does this perform against creating a view in SQL and use SQL combine instead?
SQL Views will always be faster :)
This was really helpful, you saved my life. I have a couple of concerns too.
1- How to make the dropdown list editable. For example, if the user can't find his desire in the drop-down's options and want to write a new one.
2- What is the coding language that Powerapps use ?
That's a nice idea of making a reference table editable in a dropdown but I am pretty sure you will have to write a separate screen to update the reference table. Otherwise users would add a value spelt they way THEY want to see it, not the way that it is stored in the reference table. Say you had a reference table for State and you stored Ohio in it among the other 49 or so values. And a user didn't like that but wanted to see it as OH (which is a valid abbreviation). If you let them do it as a data editor, then you will 2 different values for that wonderful state. Which makes analytics harder to do as well as searches, etc.
So in my opinion, not a good idea to do.
Yes, I think I understand what do you mean. I did something simpler to solve this issue.
So, I had a dropdown list with two options (qualified, unqualified). I wanted to give the customer the ability to enter a new option and state the problem. I made a text input for them and then added its value to my list.
So, my dropdown items looked like this: [TextInput1.Text, "Qualified","Unqalified"]. Its kinda solved the problem
If I am using PowerApps with a combination of on premises SQL Server and D365 Cloud will PowerApps use the security from D365 to limit what a user can see in both SQL Server and D365? If I am running PowerApps by personalizing D365 and embedding my app in D365.
No. You need to code for the security trimming.
Nice Video ..i have some question ..whenever we create an item in the SharePoint list , it will store the record with created by , created date , modified by , modified date which is default column in the SharePoint list ....can you please let me know how to achieve this when we are using SQL server as a data source .....
You have to create the columns in sql and then manually patch them yourself. It is one of the positives of SharePoint
Hello Shane, I trust this message finds you well. I wanted to kindly request if you could consider creating a new video on the same topic, focusing on the latest PowerApps version. It appears that the new PowerApps now facilitates app creation within a single screen, and I believe your insights on this would be incredibly valuable. Thank you in advance for your time and consideration.
I am having a little trouble - super new to PowerApps. I have an Excel spreadsheet with a number of different tables. The purpose is to have an app to access customer data for our technicians in the field. I have a main table (Customers) with a column for job type. In the same spreadsheet on another tab I have a simple table for the job types, with columns labeled JobTypeID and JobType (description). What I'm trying to do is create a dropdown giving the option to select the description, and this will then write the JobTypeID back to the Job Type field in the main Customers table. I have the dropdown pulling correctly, but it's not writing back to the main table, and I'm really not sure how to set up the LookUp Default for the Job Type card. Right now I have it as LookUp(JobTypeTable, JobTypeID = JobType). I've also tried LookUp(JobTypeTable, JobTypeID = Dropdown1.Selected.JobTypeID), and LookUp(JobTypeTable, ThisItem.'Job Type' = JobTypeTable, JobType), but I keep getting the error that I have an invalid argument type. If you could straighten this out for me, I'd appreciate it!!
I'm also having an issue with bad gateway, which I think is related to the error with the job type default.
See if this helps? ruclips.net/video/fqH1U0_TanI/видео.html
can you push MS to have the connectors included? I get hamstrung all the time because the per user lic cost is often to high or the organization so we end up on SP or excel instead.
They know people want it but I don't think they are going to change anytime soon.
Hi Shane, Is it possible to insert data into Oracle table by using PowerApps front-end? I am getting error it says Data source is read-only but I can insert easily with insert query in oracle. Thanks for this wonderful video.
I think so. Are you sure your table has a primary key? That would be my first guess.
@@ShanesCows yes I'm sure. Primery key is defined well but I cannot insert values in db by using Powerapps front-end.
Can you make vide on order details based nested table...thx
This? ruclips.net/video/nlhWQg03TL0/видео.html
Hey Shane, how come my dropdown is not set to the default one (id=0, value='') when adding a new record? It always set to the value from the record just being edited?
What does your function look like?
My function is, If(EditForm1.Mode=FormMode.Edit, LookUp('[dbo].[Preference]', PreferenceID = Preference, PreferenceDetail), LookUp('[dbo].[Preference]', PreferenceID = 0, PreferenceDetail)). And I noticed if I set the PreferenceDetail to 'N/A' for ID=0, this function will work. The problem comes out only when the PreferenceDetail is '' for ID=0.And another question is , I see you add a Refresh button to refresh the datasource, I wonder how often the PowerApp will automatically refresh the datasource?Thank you so much.
Dear Shane when I add a dropdown on the newest version cannot go under the customer Datacard and goes always on the top opf the screen. The previously menu control is the new Input on the menu correct? Solved Unlock the property
Correct. Try clicking on the data card in the tree control on the left then do the insert.
I forgot to unlock... so sorry for the question
Hi Shane, I'm confused. My organisation has historically used Sharepoint but, mainly because of your advice regarding delegation limits, I've imported some lists into Dataverse. I'm really an old Access user but I can't find any ways to graphically build queries within Dataverse.. Is there a way to do this?
I assume you mean like a query designer? Not really. If you look at DataVerse there are views. That is the best you get.
@@ShanesCows Hey Shane, thanks for the reply. I love your videos.My PowerAppAbility is coming on in leaps and bounds with your help. Keep up the great work.
thank you, great but one little addition: for the dropdown Default event handler: The logical check should include View Mode in addition to Edit mode: If(Form1.Mode = FormMode.Edit || Form1.FormMode = FormMode.View, LookUp('[dbo].[whatevs]',...
Thanks for sharing. 😀
thanks shane, how can i show the data in a gallery for my teams to see the data for each customer as it happens
You could refresh the datasource periodically if you want to get live updates
Hi Shane, I was using the Lookup function in this way LookUp('[dbo].[Potential_Header]', BrowseGallery1.Selected.Order_Number = '[dbo].[Potential_Header]'.SON, DueBy) and it didn't accept the = camparison operator, so I used this instead: LookUp('[dbo].[Potential_Header]', BrowseGallery1.Selected.Order_Number in '[dbo].[Potential_Header]'.SON, DueBy) and it returned the value it was expected. Is this a normal behavior when comparing Sharpoint fields to DB table fields?
I think the problem is in the = example the column goes first. Lookup(datasource, column = dropdown)
Thanks Shane Young , I will try it tomorrow and let you know
You were right, that was the issue. Thaks!
When the Lookup Table Column Name and the Form ColumnName are the same, How does this work?
We have a sysCountry Table which has a CurrencyId and the sysCurrency Table also has a CurrencyId, How do we match the condition.
It would be a great help if you could help us get over this.
I think answered this in another content or email 😀
hello, Mr. Shane, in the development of an application in power apps, I have found the following problem, I must relate more than two tables, that is, the procedure is a form in which a collection by means of a gallery function of sub form and to that collection I must add another table for the completion of an information. I ask for your kind cooperation if we can see in one of your videos the relationship of a form, a gallery and a gallery to a form.
Thank you for your attention
Try this video ruclips.net/video/DylxsXIUyDc/видео.html it is part 2 but I think it covers what you are hoping for.
thank you very much Mr. Shane Young, his teachings have made me improve in a new skill
Which video series demonstrates the "Big App" for sql powerapps?
Tony I think it was this one. ruclips.net/video/CO6xfbjnYwc/видео.html but not positive. If you tell me the time stamp, in a new comment, not a reply. I will watch what I said and see if I can figure out what video i meant.
Hey Shane, do you have to add a foreign key inside SQL to make this work?
Nope 😀 SQL knows nothing
@@ShanesCows Cool...check out this question I posted as a follow-up. powerusers.microsoft.com/t5/Building-Power-Apps/Dropdowns-SQL/td-p/558474
I have done exactly what you said but my detail screen is not showing the right looked up value. Its just showing the first value. However when I change the data it will be inserted and updated correctly in the database. How is this possible? My primary and foreign keys have the same name btw, but this shouldn't matter right?
in case anyone is wondering. I had to use "LookUp('[finance].[categories]'; categoryId = ThisItem.categoryId; category)" (look at the ThisItem) as a solution.
Glad you got it Jaco! Thanks for sharing
how to send data in ecrypt form to sql and get back in decrypt form throw sql by powerapp ?
The SQL data is encrypted and secured. I think you are fine but I am not a SQL or encryption expert.
It helped
Awesome 😎
How can I create a gallery with multiple data sources? eg. id from one table and name from another table displayed on the gallery list
Create a collection with the combined data you want to show. Then show the collection in the gallery.
I'm trying to create a powerapp with sql server, but when I click create automatically, the edit screen does not appear. It seems not to be allowed, can this be triggered because? I am dbo in the database.
The issue is you don’t have a primary key on your Table. Check my Azure SQL video for more details.
I'm very happy and thank you for teaching us and inspire people in the cube (kidding) Could you please show us... Lookupvalues with filters... Let's say Shane must see only dog and cats plus the blannk ID 3 and not Horses and cows...et cetera
Maybe my video on Learn to use the PowerApps Filter function ruclips.net/video/nlhWQg03TL0/видео.html
@@ShanesCows I should owe you a lot of money for the amazing free teaching lessons. Honestly, Can I do a small donation to any organization you may think in Greece on behalf Bold Zebras(anonymously * note that's a Greek word)?
Hi! Can You make an example on Track changes? For example when multiple people leave comments on tasks/lists what ever....
Hi Andis - Try this video on PowerApps audit logging. ruclips.net/video/tNKcsuL72ks/видео.html
Tried for Oracle tables but didn't work. CustomerID from projects table is not showing up in lookup function.
Hello Shane, When I share my powerpp application just for use ( not as CO-owner) with other users to use the app, they are now able to create their own Power apps applications and use the same data connections (SQL server azure database table data) That I used in my application that I shared with them. That's scary! LOL question: How to we prevent users using my powerapp application from creating their own apps with my data source connection. Thank You!
Yes. This is a struggle. I have written some notes on this. If you want to email me shane at PowerApps911 I would share with you for some feedback. At some point it will be a video.
Surely, those users don't have access to the username and password for the SQL Db or, perhaps, the connection includes those credentials?!
How to update my database table based on my input in power app
Maybe this will help? ruclips.net/video/CO6xfbjnYwc/видео.html
How can I get last value from sharepoint list, using LookUp?
exemple:
ID Name Date
1 aaa 26/04/2020
2 bbb 27/04/2020
3 aaa 27/04/2020
In this case, I just want to data from ID number 3.
Congrats
Filter the list and the use the Last function
@@ShanesCows I had done this before, I use this function, as you say. That's so hard, but worked.
"..Last(Filter(AppDriverCobProspeccao; CPF = AppDriverCobGeral[@CPF])).Menu = DropClientesInativoProspec.SelectedText.Result)..."
Thanks a lot
00:36 so cute
Thanks 😊
Really helpful video! It gave me some guidance on how to reference data tables within my powerapp.
Would it be possible to make a video explaining the basics for doing the same with common data service entities and relationships? This article from microsoft explains it briefly but its something that I'm still having some slight trouble wrapping my head around, in terms of the syntax, the dos and don'ts, etc.: powerapps.microsoft.com/en-us/blog/one-to-many-relationships-for-canvas-apps/
CDS relationships on my list. :)
@@ShanesCows Amazing!! 🤟🤟🤟
SQL Server on Azure is pricey.
DBaaS starts at $5 a month. Ignore the pricing calculator. It is super cheap.
shocking, putting garbage in Db to satisfy a UI
You can do it the other way to satisfy the dba.
If you are talking about the drop-downs you can set a default value under the advanced tab. I have a maintenance app with a table for equipment. The items for the drop-down is the equipment table. But the default on the drop-down on a new work order says "Select Equipment" because I specified that as the default.
Are you kidding me!! $40 pr user/month.. We ar 600 users. thats $24K / month :-D ridiculous..
I wish I could change it.