cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Humanity
Helper V
Helper V

The specified DataSource cannot be used with this function

I'm getting this error: The specified DataSource cannot be used with this function.

 

From the Save Button below.... Please ignore the fact I cloned the screen from electricity so there is some field name laziness... but something is not right with the DB connection.  Why are all my input fields connecting to the Google Sheets table but not the save function?  I disconnected all 3, cleared the ID columns out & then reconnected... still getting the same error.  And I get that error when I insert a brand new button.

 

 

 

SAVE BUTTON:  ONSELECT

 

 

UpdateIf(If(GasYear="1",'Natural Gas Bills',If(GasYear="2",'Natural Gas Bills 2','Natural Gas Bills 3')),

Month=GasChoice.Month,

{
Provider:'IP Elec Provider UD_15'.Text,

Cost_GJ:'IP Elec Amount Sold UD_15'.Text,

Monthy_GJ:'IP Elec Price Sold For UD_15'.Text,

Admin_Fee:'IP Elec Purchased Price UD_15'.Text,

Delivery_Fee:Text('IP Elec kWh purchased UD_15'),

Carbon_Tax:'IP Elec Prov Tax UD_15'.Text,

Prov_Tax:'IP Elec Federal Tax UD_15'.Text,

Fed_Tax:'IP Elec Transmission UD_15'.Text,

Other_Tax:'IP Elec Distribution UD_15'.Text }
)
;
Refresh('Natural Gas Bills')

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi@Humanity,

Based on the issue that you mentioned, do you want to save data back to Google sheet?

Could you please share a bit more about your scenario:

  1. What is the relationship between the 'GasYear' and the other three sheet 'Natural Gas Bills', 'Natural Gas Bills 2', 'Natural Gas Bills 3'?
  2. How you configure your three Google sheets, provide your screenshot, or something example.
  3. What is the GasChoice, is it a Dropdown or anything else? Do you refer to it to decide which record to be updated?

Are there three Google sheet, each of which has an identical GasYear column? I assume that you distinguish the three Google sheets based on the value of the 'GasYear' column. The different values of 'GasYear' correspond to different data sources depending on the situation.

I think there is something syntax wrong with your UpdateIf() function. The following is a standar formula you can refer to.

UpdateIfDataSourceCondition1ChangeRecord1 [, Condition2ChangeRecord2, ... ] )

The data source should be constant rather than a variable. You should put the If() function outside the UpdateIf(). You can check UpdateIf for details.

Further, you should refer to a value from the control within the data card rather than the column value. Replace the GasYear="1" with something like this, DataCardValue1="1".

I have a test on my side which is based on my assumption, please take the following workaround.

Connect Google Sheets connection to your App. At this step, please pay attention to the name of your sheet.

Make sure that you have referred to the correct sheet name.

Googlesheet4.png

Set the OnSelect property of the save button as below:

 

If(
    DataCardValue1.Text = "1",
    UpdateIf(
        Sheet1,
        GasChoice = Dropdown1.Selected.GasChoice,
        {
            Provider: DataCardValue4.Text,
            Cost_GJ: DataCardValue8.Text,
            Monthy_GJ: DataCardValue2.Text,
            Admin_Fee: DataCardValue6.Text,
            Delivery_Fee: DataCardValue9.Text,
            Carbon_Tax: DataCardValue7.Text,
            Prov_Tax: DataCardValue4.Text,
            Fed_Tax: DataCardValue10.Text,
            Other_Tax: DataCardValue3.Text
        }
    ),
    If(
        DataCardValue1.Text = "2",
        UpdateIf(
            Sheet1_1,
            GasChoice = Dropdown1.Selected.GasChoice,
            {
                Provider: DataCardValue4.Text,
                Cost_GJ: DataCardValue8.Text,
                Monthy_GJ: DataCardValue2.Text,
                Admin_Fee: DataCardValue6.Text,
                Delivery_Fee: DataCardValue9.Text,
                Carbon_Tax: DataCardValue7.Text,
                Prov_Tax: DataCardValue4.Text,
                Fed_Tax: DataCardValue10.Text,
                Other_Tax: DataCardValue3.Text
            }
        ),
        UpdateIf(
            Sheet1_2,
            GasChoice = Dropdown1.Selected.GasChoice,
            {
                Provider: DataCardValue4.Text,
                Cost_GJ: DataCardValue8.Text,
                Monthy_GJ: DataCardValue2.Text,
                Admin_Fee: DataCardValue6.Text,
                Delivery_Fee: DataCardValue9.Text,
                Carbon_Tax: DataCardValue7.Text,
                Prov_Tax: DataCardValue4.Text,
                Fed_Tax: DataCardValue10.Text,
                Other_Tax: DataCardValue3.Text
            }
        )
    )
)

 

Note: DataCardValue1 is my corresponding control to 'GasYear', please replace them with yours.

Best Regards,

Qi

 

 

Best Regards,
Qi

View solution in original post

4 REPLIES 4
eka24
Super User III
Super User III

Please can you explain what you are trying achieve with this portion:

UpdateIf(If(GasYear="1",'Natural Gas Bills',If(GasYear="2",'Natural Gas Bills 2','Natural Gas Bills 3')),

Month=GasChoice.Month,

Which columns are involved?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

I made 3 tables, 1 for each year.  Then I have 3 galleries stacked on one another made visible using the buttons...

 

z.PNG

v-qiaqi-msft
Community Support
Community Support

Hi@Humanity,

Based on the issue that you mentioned, do you want to save data back to Google sheet?

Could you please share a bit more about your scenario:

  1. What is the relationship between the 'GasYear' and the other three sheet 'Natural Gas Bills', 'Natural Gas Bills 2', 'Natural Gas Bills 3'?
  2. How you configure your three Google sheets, provide your screenshot, or something example.
  3. What is the GasChoice, is it a Dropdown or anything else? Do you refer to it to decide which record to be updated?

Are there three Google sheet, each of which has an identical GasYear column? I assume that you distinguish the three Google sheets based on the value of the 'GasYear' column. The different values of 'GasYear' correspond to different data sources depending on the situation.

I think there is something syntax wrong with your UpdateIf() function. The following is a standar formula you can refer to.

UpdateIfDataSourceCondition1ChangeRecord1 [, Condition2ChangeRecord2, ... ] )

The data source should be constant rather than a variable. You should put the If() function outside the UpdateIf(). You can check UpdateIf for details.

Further, you should refer to a value from the control within the data card rather than the column value. Replace the GasYear="1" with something like this, DataCardValue1="1".

I have a test on my side which is based on my assumption, please take the following workaround.

Connect Google Sheets connection to your App. At this step, please pay attention to the name of your sheet.

Make sure that you have referred to the correct sheet name.

Googlesheet4.png

Set the OnSelect property of the save button as below:

 

If(
    DataCardValue1.Text = "1",
    UpdateIf(
        Sheet1,
        GasChoice = Dropdown1.Selected.GasChoice,
        {
            Provider: DataCardValue4.Text,
            Cost_GJ: DataCardValue8.Text,
            Monthy_GJ: DataCardValue2.Text,
            Admin_Fee: DataCardValue6.Text,
            Delivery_Fee: DataCardValue9.Text,
            Carbon_Tax: DataCardValue7.Text,
            Prov_Tax: DataCardValue4.Text,
            Fed_Tax: DataCardValue10.Text,
            Other_Tax: DataCardValue3.Text
        }
    ),
    If(
        DataCardValue1.Text = "2",
        UpdateIf(
            Sheet1_1,
            GasChoice = Dropdown1.Selected.GasChoice,
            {
                Provider: DataCardValue4.Text,
                Cost_GJ: DataCardValue8.Text,
                Monthy_GJ: DataCardValue2.Text,
                Admin_Fee: DataCardValue6.Text,
                Delivery_Fee: DataCardValue9.Text,
                Carbon_Tax: DataCardValue7.Text,
                Prov_Tax: DataCardValue4.Text,
                Fed_Tax: DataCardValue10.Text,
                Other_Tax: DataCardValue3.Text
            }
        ),
        UpdateIf(
            Sheet1_2,
            GasChoice = Dropdown1.Selected.GasChoice,
            {
                Provider: DataCardValue4.Text,
                Cost_GJ: DataCardValue8.Text,
                Monthy_GJ: DataCardValue2.Text,
                Admin_Fee: DataCardValue6.Text,
                Delivery_Fee: DataCardValue9.Text,
                Carbon_Tax: DataCardValue7.Text,
                Prov_Tax: DataCardValue4.Text,
                Fed_Tax: DataCardValue10.Text,
                Other_Tax: DataCardValue3.Text
            }
        )
    )
)

 

Note: DataCardValue1 is my corresponding control to 'GasYear', please replace them with yours.

Best Regards,

Qi

 

 

Best Regards,
Qi

View solution in original post

Well as you may of guessed I'm new at this... Don't do what I did... Use your IF() statements as much as you can... including the Galleries resources.

 

The way I had done it, worked till there was an update that made it impossible for my method to work anymore.  Which is good, because it forced me to take it down to 3 buttons, a hidden dropdown (to make use of the OnChange to post button selections, to a particular cell in each of the tables.  

 

A better way than I did it though, is to populate a Year column, then sort by that when reading & writing to the database.  However I was being a bit lazy so diced up the table into 3rds.   Next time I will be reducing those DBs down to 1, and one only.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,838)