Date Calculations for Business Days

Like (0) Share

Report

Posted on 17 Feb 2022 23:05:08 by Adrian59

161

I am using a SharePoint Online list for my Non Conformance Reporting System.

I have connected a 5 Form App and I am using a Reza Dorrani formula - Date Calculations for Business Days – in my app to calculate a Due Date determined by Priority.

Everything works great except when I select a new record (Form 1) and select the Priority, the Due Date changes on all previous records in my Gallery. Even completed records. Previous records of Form 2, 3, 4 & 5 are not changed and remain correct. Only all Form 1 in my Gallery change.

Can anyone advise of a fix for this occurrence please.

Kind regards

Adrian

Help - Due Dates.pdf

All responses (11)

Answers (0)

Adrian59 161 on 21 Feb 2022 at 00:02:01

Like (0)

Report

Re: Date Calculations for Business Days

@PowerRanger
Sorry I am relatively new to this.
NCR Due Date - There is no Text Property. It is all in the Default property as shown in the previous post.

Gallery Items Property has all search functions as shown below:
If(
varDataCount < vardelegationLimit,
Filter(
Filter(
'NCR Database',
(Status in cbStatus.SelectedItems || cbStatus.Selected.Value = Blank()) && (drpCompany.Selected.Value = Blank() || 'Manager Division/Company'.Value = drpCompany.Selected.Value) && ('External Supplier'.Value = drpExtSupplier.Selected.Value || drpExtSupplier.Selected.Value = Blank())
),
textSearch.Text in 'NCR Number' || textSearch.Text in 'Responsible Manager'.DisplayName || textSearch.Text in 'Job Number' || textSearch.Text in 'Serial Number' || textSearch.Text in 'Part Number' || textSearch.Text in 'Audit Report No'
),
Filter(
'NCR Database',
(Status in cbStatus.SelectedItems || cbStatus.Selected.Value = Blank()) && (drpCompany.Selected.Value = Blank() || 'Manager Division/Company'.Value = drpCompany.Selected.Value) && ('External Supplier'.Value = drpExtSupplier.Selected.Value || drpExtSupplier.Selected.Value = Blank())
)
)
PowerRanger 3,458 Super User 2024 Season 1 on 20 Feb 2022 at 23:50:25

Like (0)

Report

Re: Date Calculations for Business Days

@Adrian59 Thats why have asked you to provide some details about your Gallery.

1. What do you use in your Gallery in the Items Property
2. What formular is in the Text Property of the Label (marked in yellow) on the Gallery
Adrian59 161 on 20 Feb 2022 at 23:47:16

Like (0)

Report

Re: Date Calculations for Business Days

@PowerRanger
Items - Choices([@'NCR Database'].'NCR Priority')
Adrian59 161 on 20 Feb 2022 at 23:43:56

Like (0)

Report

Re: Date Calculations for Business Days

@PowerRanger
Sorry I missed your first question.
All items in my SharePoint List and Gallery do get updated using Submit. Its just when I select a new Form 1 and enter the new NCR Due Date, all previous/current Form 1 records in The gallery change to the new date entered. All other DataCardValues do not change.
formular is in Default - Last(FirstN(colDateRange,NumberOfDays)).Date
PowerRanger 3,458 Super User 2024 Season 1 on 20 Feb 2022 at 23:18:39

Like (0)

Report

Re: Date Calculations for Business Days

@Adrian59 If you use a Submit() there is actually no way that ALL Items in your SharePoint List get updated. So it must be something wrong on your Gallery. Could you answer my first question from my previous post? And also provide the Formular you use for the Items Property of the Gallery.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Adrian59 161 on 20 Feb 2022 at 22:58:04

Like (0)

Report

Re: Date Calculations for Business Days

I use a Form and Submit(Form1)
PowerRanger 3,458 Super User 2024 Season 1 on 20 Feb 2022 at 22:40:44

Like (0)

Report

Re: Date Calculations for Business Days

@Adrian59 What formular is used in the Text Property of this Label in the Gallery
How do you save NEW records in you SharePoint List? Using a form and Submit(Form1) or do you have a custom Patch() function?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Adrian59 161 on 20 Feb 2022 at 22:20:44

Like (0)

Report

Re: Date Calculations for Business Days

G'day @PowerRanger .
Data source is a Online SharePoint list.
I use a Canvas PowerApp to add, amend and close records.
PowerRanger 3,458 Super User 2024 Season 1 on 20 Feb 2022 at 22:05:34

Like (0)

Report

Re: Date Calculations for Business Days

@Adrian59 could you provide some additional details? What's the datasource of the Gallery? How do you add new records? Which Formular do you use for adding new records?
Adrian59 161 on 20 Feb 2022 at 21:48:47

Like (0)

Report

Re: Date Calculations for Business Days

Clear(colDateRange);
ForAll(
    [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,2122,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
],
    Collect(
        colDateRange,
        {
            RowIndex: CountRows(colDateRange) + 1,
            Date: DateAdd(
                Today(),
                Value
            )
        }
    )
);
RemoveIf(
    colDateRange,
    Weekday(Date) = 1 || Weekday(Date) = 7 || CountRows(
        Filter(
            colDateRange,
            NumberOfDays
        )
    ) > 0
);