cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FelipeCaru
Advocate V
Advocate V

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 😧 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
v-xida-msft
Community Support
Community Support

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
v-xida-msft
Community Support
Community Support

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

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (28,974)