submitting 2 forms if total hours = over 8.

[Anonymous]

Hello!

I am having a ticket system where workers can clock-in during their worktime. The app looks like this: https://gyazo.com/c8761eaa962db09ceb8dff502ba24321

Database looks like this:
https://gyazo.com/bccfff0f750091f347877325f6d67d82
Whenever the column tijdtotaal of the day they are wanting to fill in a ticket, is higher than 8, I want it to make a new ticket with the rest of the hours. 

This is what we need to do: 

-Everytime a new ticket gets created, we need to filter the database on the date of today and see how many rows are there. It then needs to check if TijdTotaal is over 8. When it is over 8, I want it to match up exactly to 8. This may be confusing so I will explain it with a screenshot.

How it is now: https://gyazo.com/b979d3d96597e7eeb8a351dfd4b8a433
How it should look like: https://gyazo.com/f256e7f855acaf9419592bae5bbbe3ff

It needs to calculate it to 8 and use the rest of the time to make a new ticket with the same information as the last ticket. How can we get this?

Just using this code to send the form away: SubmitForm(EditForm1)

I hope I have explained this well enough, if I didn't, let me know and I will try and explain it better. 

[Mr-Dang-MSFT]

Hi @Anonymous,

If I understand correctly:

• You are keeping track of time in a SharePoint list.
• When tijd totaal exceeds 8 hours, you want to enter a record that contains the difference between tijd totaal and 8 hours.
• Then you create an additional record for the amount of tijd totaal above 8 using the same details.

The steps to solving this might look like this:

1. Get the records for the day. Hold this in a collection so that you can make calculations with its data (colUrenRegistratie in the formula below).
2. Calculate a sum of tijd totaal for the day. We will use this to determine how many hours to record.
3. Use Mod() to calculate the remainder if you divide the sum of tijd totaal by 8. If the remainder is 0, assume it is 8 instead. Using a remainder will help the solution scale so that you can adjust for every multiple of 8 hours: 8, 16, 24...
4. Use a condition to compare the remainder with the tijd totaal that the user submits.
   • If the submission of tijd totaal is less than or equal to the remainder, use SubmitForm only. This is the formula you already have.
   • If the submission of tijd totaal is greater than then remainder:
     • Use a condition to check if the form entry is valid. If it is, proceed to the next step. If the entry is not valid, show an error to the user. This step is needed since Patch() does not use the error handling that the Form control has.
     • Use a Patch() statement to create a record submitting the remainer as tijd totaal.
     • Use a second Patch() statement to create a record submitting the difference between the tijd totaal and the remainder.
     • Show an error if there are any errors during Patch.
5. If successful, reset the form and navigate. The same formula needs to be in EditForm1's OnSuccess property.

The button that you use for submitting the form might contain a formula like this in the OnSelect property:

// Get the records for today.
// Revise the filter with the conditions you need.
ClearCollect(colUrenRegistratie,Filter(UrenRegistratie,...));

With(
    {
        // Calculate a sum of the tijd totaal for today.
        sumTijdTotaal: Sum(colUrenRegistratie,TijdTotaal)
    },
    With(
        {
            // Calculate a remainder of the tijd totaal if you divide its sum by 8.
            // If the remainder is 0, then make it 8.
            remainder: If(Mod(sumTijdTotaal,8)=0,8,Mod(sumTijdTotaal,8))
        },
        If(
            // Check if one or more records needs to be created.
            EditForm1.Updates.TijdTotaal<=8,

            // CASE: The tijd totaal is less than or equal to 8 and only one record needs to be created.
            SubmitForm(EditForm1),

            // CASE: The tijd totaal exceeds the remainder and multiple records need to be created.
            If(
                // Check if the entry in the form is valid.
                !EditForm1.Valid,
                
                // ERROR: Show an error if the form was not entered correctly.
                Notify("One or more fields in the form were not entered correctly.",NotificationType.Error),

                // Create a record submitting hours equal to the remainder.
                Patch(UrenRegistratie,EditForm1.Updates,{TijdTotaal: remainder});

                // Create a second record submitting hours equal to the difference between the entered tijd totaal and the remainder.
                Patch(UrenRegistratie,EditForm1.Updates,{TijdTotaal: EditForm1.Updates.TijdTotaal-remainder});

                If(
                    // Check for any errors in patching.
                    !IsEmpty(Errors(UrenRegistratie)),

                    // ERROR: Show an error if the data source had errors during the Patch.
                    Notify("There was an error submitting the form and records may not have been submitted successfully.",NotificationType.Error),;

                    // Reset the form and navigate away upon success.
                    // Place these same actions in the OnSuccess property of EditForm1.
                    ResetForm(EditForm1);
                    Navigate(Screen1,Fade)
                )
            )
        )
    )
)

Let me know which parts need clarification.

[Anonymous]

Hello @Mr-Dang-MSFT 

At first I want to thank you for your answer. I'm just a little bit confused about some parts. 

I need to put the code on the editform succes & on the onselect of the button? @Mr-Dang-MSFT 

I get this error (I didn't put the code on the editform succes yet): https://gyazo.com/7f6fd2bea04b47879074fe1cce1224e4

Don't I need to add newform(editform1) somewhere?

Currently this is the code: 

// Get the records for today.
// Revise the filter with the conditions you need.
ClearCollect(colUrenRegistratie, Filter(
        UrenRegistratie,
        Datum = Text(
            Today (),
            "dd/mm/yyyyy"
        ) And varcurrentuser = Werknemer
    ));

With(
    {
        // Calculate a sum of the tijd totaal for today.
        sumTijdTotaal: Sum(colUrenRegistratie,TijdTotaal)
    },
    With(
        {
            // Calculate a remainder of the tijd totaal if you divide its sum by 8.
            // If the remainder is 0, then make it 8.
            remainder: If(Mod(sumTijdTotaal,8)=0,8,Mod(sumTijdTotaal,8))
        },
        If(
            // Check if one or more records needs to be created.
            EditForm1.Updates.TijdTotaal<=8,

            // CASE: The tijd totaal is less than or equal to 8 and only one record needs to be created.
            SubmitForm(EditForm1),

            // CASE: The tijd totaal exceeds the remainder and multiple records need to be created.
            If(
                // Check if the entry in the form is valid.
                !EditForm1.Valid,
                
                // ERROR: Show an error if the form was not entered correctly.
                Notify("One or more fields in the form were not entered correctly.",NotificationType.Error),

                // Create a record submitting hours equal to the remainder.
                Patch(UrenRegistratie,EditForm1.Updates,{TijdTotaal: remainder});

                // Create a second record submitting hours equal to the difference between the entered tijd totaal and the remainder.
                Patch(UrenRegistratie,EditForm1.Updates,{TijdTotaal: EditForm1.Updates.TijdTotaal-remainder});

                If(
                    // Check for any errors in patching.
                    !IsEmpty(Errors(UrenRegistratie)),

                    // ERROR: Show an error if the data source had errors during the Patch.
                    Notify("There was an error submitting the form and records may not have been submitted successfully.",NotificationType.Error);

                    // Reset the form and navigate away upon success.
                    // Place these same actions in the OnSuccess property of EditForm1.
                    ResetForm(EditForm1);
                    Navigate([@Overzicht],Fade)
                )
            )
        )
    )
)

[Mr-Dang-MSFT]

Hi @Anonymous ,

We wrote these errors ourselves using the Notify() function. So since you received that error message, it means:

• ✅ The tijd totaal exceeded 8 hours.
• ✅ The entry in the form was valid.
• ❌ One or more of the patch statements we wrote did not work.

I'm thinking the Patch() might work better if we include Defaults(UrenRegistratie) as the second argument.

• This provides the patch with a full blank record; EditForm1.Updates includes a record from what is entered into the form; and the {TijdTotaal: ___} replaces the intended value for tijd totaal from the form.
• Patch() smashes all those records together with any duplicate columns with a value equal to the last one listed.
• It would look something like this:

// Create a record submitting hours equal to the remainder.
Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: remainder});

// Create a second record submitting hours equal to the difference between the entered tijd totaal and the remainder.
Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: EditForm1.Updates.TijdTotaal-remainder});

If that does not work, can you insert a temporary button with just the patch statements, fill in a new form with a tijd totaal > 8 hours, click the temporary button, and see what error happens?

[Anonymous]

So basically I have changed the code like you told me to, looks like this now: 

// Get the records for today.
// Revise the filter with the conditions you need.
ClearCollect(colUrenRegistratie, Filter(
        UrenRegistratie,
        Datum = Text(
            Today (),
            "dd/mm/yyyyy"
        ) And varcurrentuser = Werknemer
    ));

With(
    {
        // Calculate a sum of the tijd totaal for today.
        sumTijdTotaal: Sum(colUrenRegistratie,TijdTotaal)
    },
    With(
        {
            // Calculate a remainder of the tijd totaal if you divide its sum by 8.
            // If the remainder is 0, then make it 8.
            remainder: If(Mod(sumTijdTotaal,8)=0,8,Mod(sumTijdTotaal,8))
        },
        If(
            // Check if one or more records needs to be created.
            EditForm1.Updates.TijdTotaal<=8,

            // CASE: The tijd totaal is less than or equal to 8 and only one record needs to be created.
            SubmitForm(EditForm1),

            // CASE: The tijd totaal exceeds the remainder and multiple records need to be created.
            If(
                // Check if the entry in the form is valid.
                !EditForm1.Valid,
                
                // ERROR: Show an error if the form was not entered correctly.
                Notify("One or more fields in the form were not entered correctly.",NotificationType.Error),

                // Create a record submitting hours equal to the remainder.
Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: remainder});

// Create a second record submitting hours equal to the difference between the entered tijd totaal and the remainder.
Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: EditForm1.Updates.TijdTotaal-remainder});

                If(
                    // Check for any errors in patching.
                    !IsEmpty(Errors(UrenRegistratie)),

                    // ERROR: Show an error if the data source had errors during the Patch.
                    Notify("There was an error submitting the form and records may not have been submitted successfully.",NotificationType.Error);

                    // Reset the form and navigate away upon success.
                    // Place these same actions in the OnSuccess property of EditForm1.
                    ResetForm(EditForm1);
                    Navigate([@Overzicht],Fade)
                )
            )
        )
    )
)

I basically changed the 2 patch lines for the new lines of code you sent. This does send the forum to my database, but does not split up the last one. Here is a screenshot: https://gyazo.com/0bd5bb68bcc7725435e77891c4f3f7d1

It is supposed to split up last row in 2 rows, since tijdtotaal = higher than 8. Second row should have (8-7) 1 and third should have 5 hours @Mr-Dang-MSFT 

[Mr-Dang-MSFT]

Oops, I did not do the math right on the remainder. I think this should do the trick; revise the calculation at the top with this:

remainder: 8-Mod(sumTijdTotaal,8)

So if the sum of existing hours was 7 (or 15 or 23), Mod() will divide that number by 8 as many times as it can and get what is left: 7. Then you subtract that amount from 8 to get how many more hours you can apply until you reach 8: 1.

The first Patch statement writes the remainder amount.

The second Patch statement takes the tijd totaal you were trying to write and applies the hours minus the remainder from the first patch.

Let me know if I got that right.

[Anonymous]

This has almost worked! We're getting close! This is what's happening now:

I made ticket with 6 hours, all good.

Made a ticket with 4 hours and 15 minutes (4,25) 

Got the error message. When I look in the database, I do see this, a ticket with 2 hours. So that's good. And the other 2,25 hours are just gone now. @Mr-Dang-MSFT 

So we just need to other ticket to get created

Database: https://gyazo.com/1b4e633bf0a249d6fd13668e311bcc85

Really appreciate the help! Stay safe!


-----------------------------------------------------------------------
Edit:
-----------------------------------------------------------------------

I tested it again today and I made a ticket of 6 hours and a ticket of 3 hours, and nothing happend. They did not get split up. https://gyazo.com/3608c9c1608e4377fb6e12f3d6e8b68f

What do I have to put on the OnSuccess of the form? (I've put the exact same code in but then it just keeps on loading when I try to send a ticket.

I made a ticket with 10 hours, and it just made it 7 hours and the other hours are gone, no other ticket gets created
----------------------------------------------------------------
When I only put in the button with the 2 patch statements, I get this error, it can't find remainder too. https://gyazo.com/53d2466928afa6c3392a209dbcb86c96

[Mr-Dang-MSFT]

Hi @Anonymous ,

These are some general strategies for seeing where a formula goes wrong for a scenario like this. It looks like the first patch is successful. To test what's going wrong with the second one:

• Insert a button. In its OnSelect property, use Set() to set multiple variables to the values used in the With() statements provided in previous responses: sumTijdTotaal, remainder.
• Insert a label on the screen and make it show the values of the variables you made during the step above.
• Insert a label on the screen to show the value of EditForm1.Updates.TijdTotaal.

Find out which of those numbers is causing the second patch statement to fail.

[Anonymous]

For the first step, are you able to provide a full code that I can copy paste? @Mr-Dang-MSFT 

I do not get any error anymore when I try to run the code. Here is the result, this is not what it is supposed to be doing. This should've been split it as a ticket with 2 hours and 4 hours.

Database image: https://gyazo.com/9db333feaeecd785b786755e21ec804e

I removed all the tickets and made a new ticket with 9 hours. It creates the ticket with 8 hours and I then get this error.  https://gyazo.com/e5db42f51519f9f20456fcd30929cc85

But whenever I make 2 tickets of 6 hours, nothing happends, when it should actually split the second one up to 2 and 4 hours.

So first I added a ticket of 8 hours. Worked good. I then made one with 6 hours, worked good too. I then made one with 9 hours and it did take 2 hours only, but then it doesn't create the new ticket sadly

Now I tried it with doing a ticket of 8 first and then it doesn't work. It does not split up the 2 tickets. https://gyazo.com/72861baf66bee9bed75c963bee3ff361

Currently code: 

ClearCollect(colUrenRegistratie, Filter(
        UrenRegistratie,
        Datum = Text(
            Today (),
            "dd/mm/yyyyy"
        ) And varcurrentuser = Werknemer
    ));

With(
    {
        sumTijdTotaal: Sum(colUrenRegistratie,TijdTotaal)
    },
    With(
        {
            remainder: 8-Mod(sumTijdTotaal,8)
        },
        If(
            EditForm1.Updates.TijdTotaal<=8,
            SubmitForm(EditForm1),
            If( 
                !EditForm1.Valid,
                Notify("One or more fields in the form were not entered correctly.",NotificationType.Error),
                Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: remainder});
                Patch(UrenRegistratie,Defaults(UrenRegistratie),EditForm1.Updates,{TijdTotaal: EditForm1.Updates.TijdTotaal-remainder});

                If(
                    !IsEmpty(Errors(UrenRegistratie)),

                    Notify("There was an error submitting the form and records may not have been submitted successfully.",NotificationType.Error);
                    ResetForm(EditForm1);
                    Navigate([@Overzicht],Fade)
                )
            )
        )
    )
)