The specified DataSource cannot be used with this function

[Humanity]

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')

[eka24]

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.

[Humanity]

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

[v-qiaqi-msft · Accepted solution]

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.

UpdateIf( DataSource, Condition1, ChangeRecord1 [, Condition2, ChangeRecord2, ... ] )

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.

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

[Humanity]

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.