The Dynamics GP Blogster: June 2020

Friday, June 19, 2020

Power Automate: Building a REST endpoint with the HTTP Request connector

In this video I show you how to build a REST endpoint with the Power Automate HTTP Request connector to be used as a web service to transfer data to a on-premises SQL database. In the process, I will be using Postman API development platform to test the endpoint and submit data to a Microsoft Dynamics GP company database via its eConnect API stored procedures.

To check out my YouTube channel visit: https://youtube.com/user/mgomezb1

LIKE AND SUBSCRIBE!!!

Until next post!

MG.-
Mariano Gomez, MVP

Wednesday, June 10, 2020

Power Apps: Calculating various dates with date functions

In this article, I will show you how to leverage the DateDiff(), DateAdd(), Weekday(), DateValue(), and Day(), and Today() functions to calculate all sort of dates that are typical to business applications: first and last day of previous month, first and last day of previous quarter, first and last day of previous week, first and last day of current month, first and last day of current quarter, first and last day of current week, first and last day of next month, first and last day of next quarter, first and last day of next week, etc.

Let's begin:

Each calculation is self-contained for convenience sake, which means the only dependency is on the cdt context variable.

Current Date

UpdateContext({cdt:Today()});

Note: you could also use the Now() function, but be aware that this function also includes the timestamp.

First Day of Previous Month

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            mts: DateDiff(
                rootdate,
                cdt,
                Months
            ) - 1
        },
        DateAdd(
            rootdate,
            mts,
            Months
        )
    )
)

Last Day of Previous Month

DateAdd(cdt, -Day(cdt), Days)

First Day of Current Month

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            mts: DateDiff(
                rootdate,
                cdt,
                Months
            )
        },
        DateAdd(
            rootdate,
            mts,
            Months
        )
    )
)

Last Day of Current Month

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            mts: DateDiff(
                rootdate,
                cdt,
                Months
            ) + 1
        },
        With(
            {
                FirstNxtMonth: DateAdd(
                    rootdate,
                    mts,
                    Months
                )
            },
            DateAdd(
                FirstNxtMonth,
                -1,
                Days
            )
        )
    )
)

First Day of Next Month

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            mts: DateDiff(
                rootdate,
                cdt,
                Months
            ) + 1
        },
        DateAdd(
            rootdate,
            mts,
            Months
        )
    )
)

Last Day of Next Month

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            mts: DateDiff(
                rootdate,
                cdt,
                Months
            ) + 2
        },
        With(
            {
                FirstNxtMonth: DateAdd(
                    rootdate,
                    mts,
                    Months
                )
            },
            DateAdd(
                FirstNxtMonth,
                -1,
                Days
            )
        )
    )
)

First Day of Previous Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qtrs: DateDiff(
                rootdate,
                cdt,
                Quarters
            )
        },
        DateAdd(
            rootdate,
            qtrs - 1,
            Quarters
        )
    )
)

Last Day of Previous Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qrtr: DateDiff(
                rootdate,
                cdt,
                Quarters
            )
        },
        With(
            {
                NxtQrtr: DateAdd(
                    rootdate,
                    qrtr,
                    Quarters
                )
            },
            DateAdd(
                NxtQrtr,
                -1,
                Days
            )
        )
    )
)

First Day of Current Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qtrs: DateDiff(
                rootdate,
                cdt,
                Quarters
            )
        },
        DateAdd(
            rootdate,
            qtrs,
            Quarters
        )
    )
)

Last Day of Current Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qrtr: DateDiff(
                rootdate,
                cdt,
                Quarters
            ) + 1
        },
        With(
            {
                NxtQrtr: DateAdd(
                    rootdate,
                    qrtr,
                    Quarters
                )
            },
            DateAdd(
                NxtQrtr,
                -1,
                Days
            )
        )
    )
)

First Day of Next Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qrtr: DateDiff(
                rootdate,
                cdt,
                Quarters
            ) + 1
        },
        With(
            {
                NxtQrtr: DateAdd(
                    rootdate,
                    qrtr,
                    Quarters
                )
            },
            DateAdd(
                NxtQrtr,
                0,
                Days
            )
        )
    )
)

Last Day of Next Quarter

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            qrtr: DateDiff(
                rootdate,
                cdt,
                Quarters
            ) + 2
        },
        With(
            {
                NxtQrtr: DateAdd(
                    rootdate,
                    qrtr,
                    Quarters
                )
            },
            DateAdd(
                NxtQrtr,
                -1,
                Days
            )
        )
    )
)

First Day of Previous Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset - 7,
            Days
        )
    )
)

Last Day of Previous Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset - 1,
            Days
        )
    )
)

First Day of Current Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset,
            Days
        )
    )
)

Last Day of Current Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset + 6,
            Days
        )
    )
)

First Day of Next Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset + 7,
            Days
        )
    )
)

Last Day of Next Week

With(
    {rootdate: DateValue("01/01/1900")},
    With(
        {
            diffval: DateDiff(
                rootdate,
                cdt,
                Days
            ),
            Offset: 1 - Weekday(cdt)
        },
        DateAdd(
            cdt,
            Offset + 13,
            Days
        )
    )
)

You can download the application on the Power Apps Community apps gallery

To check out my YouTube channel visit: https://youtube.com/user/mgomezb1
LIKE AND SUBSCRIBE!!!

Until next post!

MG.-
Mariano Gomez, MVP

Monday, June 8, 2020

Power Apps: Mixed Reality spatial controls

For the final installment of the Power Apps Mixed Reality series, I'm going to show you how to use the MR spatial controls, Shape in MR and Measure in MR. You will get to understand a bit more about the available properties for these controls and how they interact with collections and galleries to produce an app.

To check out my YouTube channel visit: https://youtube.com/user/mgomezb1

LIKE AND SUBSCRIBE!!!

Until next post!

MG.-
Mariano Gomez, MVP

Disclaimers

This blog and its content is provided "AS IS" with no warranty of any kind, either expressed or implied, made in relation to the accuracy, reliability or content of the information herein. The author shall not be liable for any direct, indirect, incidental or consequential damages arising out of the use of, orinability to use, information or opinions expressed in this site and confers no rights.

ALL SCRIPTS ON THIS SITE ARE DISTRIBUTED IN THE HOPE THAT THEY WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. THEY ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF SUCH SCRIPTS IS WITH YOU. SHOULD ANY SCRIPT PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW THE AUTHOR WILL BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE ANY SCRIPTS (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE SCRIPTS TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

The links in this blog may lead to third-party Web sites. These third-party resources are provided to help you find other services and/or technical support resources. Information at these sites may change without notice. I am not responsible for the content at any third-party web sites and do not guarantee the accuracy of such third-party information.