cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FelipeCaru
Level 8

Input data in form and Excel formula

Hello all,

I have a question regarding Excel formula. If I have, for example, 4 fields in my Excel source/database such as:

- Column A: task name (filled in source)

- Column B: Start date planned (filled in source)

- Column C: Start date real (empty, filled via PowerApps form)

- Column D: IF(startDateReal<startDatePlanned, "started before", 0)

Column D also has that if "start date real" is empty, then it shows nothing ( "" ). My question is, if the user inputs a date in the form, which will fill Column C, will the formula work and fill the Column D and thus show the updated field in the app?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Input data in form and Excel formula

Hi @FelipeCaru ,

Could please share a bit more about your scenario?

Do you use the IF function in your Excel table to calculate the Column D value (calculated column)?

Further, do you would like the Calculated column (Column D) to be populated automatically within your app?

 

Based on the needs that you mentioned, I afraid that there is no way to achieve your needs in PowerApps currently.

Firstly, within PowerApps, we could not create a connection to a Excel table which has a calculated column from a canvas app currently.

In addition, the Excel formula (e.g. IF function that you mentioned) would also not be applied to a canvas app.

 

As an alternative solution, you could consider do the calculation operation for Column D within your canvas app rather than use the Excel formula in your Excel table itself.

I have made a test on my side, please take a try with the following workaround:

My Excel Table as below:1.JPG

Note: The Start date planned column and Start date real column are both Date type columns in your Excel table.

My App's configuration as below:2.JPG

 

3.JPG

On your side, you could generate an app based on your Excel table, then it would generate three screens automatically as above I mentioned.

Within the Edit form of the Edit screen, set the Update property of the Column D Data card to following:

If(
    IsBlank('Start date real _DataCard2'.Update),
    "",
    If(
        'Start date real _DataCard2'.Update < 'Start date planned_DataCard2'.Update,
        "started before",
        0
    )
)

Note: The 'Start date real _ DataCard2' represents the Start date real data card in your Edit form, the 'Start date planned_ DataCard2' represents the Start date planned data card in your Edit form.

Please check the following screenshot for more details:Test.gif

 

More details about generating an app based on Excel table, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Input data in form and Excel formula

Hi @FelipeCaru ,

Could please share a bit more about your scenario?

Do you use the IF function in your Excel table to calculate the Column D value (calculated column)?

Further, do you would like the Calculated column (Column D) to be populated automatically within your app?

 

Based on the needs that you mentioned, I afraid that there is no way to achieve your needs in PowerApps currently.

Firstly, within PowerApps, we could not create a connection to a Excel table which has a calculated column from a canvas app currently.

In addition, the Excel formula (e.g. IF function that you mentioned) would also not be applied to a canvas app.

 

As an alternative solution, you could consider do the calculation operation for Column D within your canvas app rather than use the Excel formula in your Excel table itself.

I have made a test on my side, please take a try with the following workaround:

My Excel Table as below:1.JPG

Note: The Start date planned column and Start date real column are both Date type columns in your Excel table.

My App's configuration as below:2.JPG

 

3.JPG

On your side, you could generate an app based on your Excel table, then it would generate three screens automatically as above I mentioned.

Within the Edit form of the Edit screen, set the Update property of the Column D Data card to following:

If(
    IsBlank('Start date real _DataCard2'.Update),
    "",
    If(
        'Start date real _DataCard2'.Update < 'Start date planned_DataCard2'.Update,
        "started before",
        0
    )
)

Note: The 'Start date real _ DataCard2' represents the Start date real data card in your Edit form, the 'Start date planned_ DataCard2' represents the Start date planned data card in your Edit form.

Please check the following screenshot for more details:Test.gif

 

More details about generating an app based on Excel table, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FelipeCaru
Level 8

Re: Input data in form and Excel formula

Thank you, that helped me @v-xida-msft (:

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 97 members 4,299 guests
Please welcome our newest community members: