Trying to build a SQL insert statement with values from dropdown lists

[bbrugg]

Hello all! I could use some help. I'm brand new to Power Apps, but I'm starting to get it! I'm having trouble, however, with trying to create an SQL insert statement (I guess it would be a patch function?) out of values from dropdowns. I have a SQL Inventory table that records instance names (table name: SQLInstanceInventory), database names (table name: DatabaseInventory), and application names. And then we relate each of the databases to the app names for our metadata in a reference table named ApplicationDatabaseInventory. 

I have dropdowns for instance name (dropdown 2), database name (dropdown 3), and application name (dropdown 1). When I choose the correct combination, the form needs to create a record in the ApplicationDatabaseInventory with the database id and application id--relating them.

This is easy to do in SQL, but I don't know if it is possible in PowerApps. The patch function would basically have to pass in a parameter from the dropdown for the values to insert...Here is my rough syntax using the "patch" function. Can someone give me some pointers or even if this can be done at all? 

Patch(ApplicationDatabaseInventory, Defaults (ApplicationDatabaseInventory),
{ApplicationId:(Select ApplicationId where ApplicationName = Dropdown1.Selected.Value),
{DatabaseId:(Select DatabaseId from DatabaseInventory di inner join SQLInstanceInventory ii where di.DatabaseName = Dropdown3.Selected.Value and ii.SQLInstanceName = Dropdown2.SelectedValue)})

[SpongYe · Accepted solution]

Hi @bbrugg 

To create an SQL insert statement with values from dropdowns, you need to use the Patch function with the appropriate syntax. In your case, the data source is ApplicationDatabaseInventory, the record to create is Defaults (ApplicationDatabaseInventory), and the changes are the values of ApplicationId and DatabaseId from the dropdowns.

You need to use the LookUp function to find the matching records from the other tables based on the selected values. Here is an example of how you can write your Patch function:

Patch(
    ApplicationDatabaseInventory, 
    Defaults (ApplicationDatabaseInventory), 
    {
        ApplicationId: LookUp(ApplicationNameTable, ApplicationName = Dropdown1.Selected.Value, ApplicationId), 
        DatabaseId: LookUp(DatabaseInventory, DatabaseName = Dropdown3.Selected.Value && SQLInstanceName = Dropdown2.SelectedValue, DatabaseId)
    }
)

This will create a new record in ApplicationDatabaseInventory with the ApplicationId and DatabaseId that match the selected values from the dropdowns.

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[bbrugg]

Thank you so much! This really pointed me in the right direction. I had to tweak things a little, and this is my final code (also renaming the dropdowns for clarity):

Patch(
ApplicationDatabaseInventory,
Defaults (ApplicationDatabaseInventory),
{
ApplicationId: LookUp(ApplicationInventory,ApplicationName = DropdownApp.SelectedText.Value,ApplicationId),
DatabaseId: LookUp(DatabaseInventory, DatabaseName = DropdownDatabase.SelectedText.Value && SQLInstanceId = (LookUp(SQLInstanceInventory,SQLInstanceName = DropdownInstance.SelectedText.Value,SQLInstanceId)),DatabaseId)
}
)