cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

Re: The specified DataSource cannot be used with this function

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

 

 

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: The specified DataSource cannot be used with this function

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.

Highlighted
Helper V
Helper V

Re: The specified DataSource cannot be used with this function

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

 

z.PNG

Highlighted
Microsoft
Microsoft

Re: The specified DataSource cannot be used with this function

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

 

 

View solution in original post

Highlighted
Helper V
Helper V

Re: The specified DataSource cannot be used with this function

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (7,618)