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

View solution in original post

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.

View solution in original post

FelipeCaru
Level 8

Re: Input data in form and Excel formula

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

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

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

Top Kudoed Authors
Users Online
Currently online: 71 members 3,478 guests
Please welcome our newest community members: