Date Filter and Sort Formula combine

[Big_S]

Hi,
Does anyone know how to combine the Sort formula to Filter Formula? I added Date filters to the gallery so it can be selected through date range from-to


Filter(MasterCollection, ApprovalStatus = Dropdown3.Selected.Result && Requester=_myProfile.UserPrincipalName)

Sort(
If(IsBlank(RequestDates_1.SelectedDate),
If(IsBlank(RequestDates),
MasterCollection,
Filter(MasterCollection,TransactionDate >= RequestDates.SelectedDate)
),
If(IsBlank(RequestDates),
Filter(MasterCollection,TransactionDate <= RequestDates_1.SelectedDate),
Filter(MasterCollection,TransactionDate <= RequestDates_1.SelectedDate,TransactionDate >= RequestDates.SelectedDate)
)
),
"TransactionDate",Descending)

[WarrenBelz · Accepted solution]

Hi @Big_S ,

Something like this

Sort(
   Filter(
      MasterCollection, 
      ApprovalStatus = Dropdown3.Selected.Result && Requester=_myProfile.UserPrincipalName &&
      If(
         IsBlank(RequestDates_1.SelectedDate) && IsBlank(RequestDates),
         TransactionDate >= RequestDates.SelectedDate,
         If(
            IsBlank(RequestDates),
            TransactionDate <= RequestDates_1.SelectedDate,
            TransactionDate <= RequestDates_1.SelectedDate && TransactionDate >= RequestDates.SelectedDate
         )
      )
   ),			
   "TransactionDate",
   Descending
)

Note this is free-typed syntax guidance, so check commas and brackets etc.

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.

[Big_S]

thank you @WarrenBelz , may I ask also why my collection won't save data? can I post my created collection here?

[WarrenBelz]

@Big_S ,

Are you saying it will not collect data (there is nothing in it) or you cannot save it to your data source?

[Big_S]

@WarrenBelz 

Yes I think it wont save because everytime I create a data after clicking the save button the data will display on the gallery but when I closed my app then open again the data was not save here's my formula. I replace my dbo.Request to MasterCollection and dbo.RequestDetail to RequestDetailCollection

If(
_submittingRequest,
Concurrent(
Set(
_navMenuSelect,
""
),
//if editing a request, revise the record that is being edited, otherwise create a new one
Patch(
MasterCollection,
If(
_editingRequest,
LookUp(
MasterCollection,
Id = GalleryRequests.Selected.Id
),
Defaults(MasterCollection)
),
{
Id: Value(Id.Text),
Description: ItemDescriptionTxtInput.Text,
Team: TeamTextInput.Text,
Type: TypeOfRequestDropDown.SelectedText.Value,
Approver: Office365Users.UserProfile(Label1_10.Text).Mail,
ApprovalStatus: "Pending",
Amount: Value(TotalAmount.Text),
//PayTo: SupplierDropDown.SelectedText.Value,
Company: TeamTextInput_2.Text,
InvoiceNumber: InvoiceNumber.Text,
PayTo: SupplierComboBox.Selected.Name,
//Supplier: SupplierDropDown.SelectedText.Value,
VAT: VATDropdown.SelectedText.Value,
TransactionDate: transactionDate.SelectedDate,


// ChargeToAccount: PaymentChargeAcct,
SpecialPaymentInstruction: SpecialPaymentTxtInput.Text,
Requester: User().Email,
Status: 1,
RequesterName: User().FullName,
Image: AttachmentTextInput.Text,
DueDate: StartDatePicker.SelectedDate,
ApproverName: Office365Users.UserProfile(Label1_10.Text).DisplayName,
Department: DepartmentComboBox.Selected.Result,
Project: ProjectComboBox.Selected.Result,
Program: ProgramComboBox.Selected.Result,
Activity: ActivityComboBox.Selected.Result,
VendorCode: supplier.Text
//Department: DepartmentDropDown_1.SelectedText.Value,
//Project: ProjectDropDown_2.SelectedText.Value,
//Program: ProgramDropdown.SelectedText.Value,
//Activity: ActivityDropDown_3.SelectedText.Value,


}
)
);
PowerAppsbutton_2.Run("Request", "N/A", User().Email, GalleryRequests.Selected.Id);
);

ForAll(
AddDetail,
Patch(
RequestDetailCollection,
Defaults(RequestDetailCollection),
{
RequestId: Last(MasterCollection).Id,
Amount: Value(PaymentAmt),
ChargeToAccount: PaymentChargeAcct,
Status: 1,
BusinessUnit: BusinessUnit,
Department: Department,
Program: Program,
Project: Project,
Activity: Activity,
AcctCode: AcctCode.Text
}
)
);

If(
!_editingRequest,
Flow20200324.Run(Last(MasterCollection).Id),
Flow20200324.Run(_selectedGalleryRequests.Id)
);
//reset all user input fields
Concurrent(
Set(
_submittingRequest,
false
),
Set(
_SequenceNumber,
""
),
Set(
_ItemDescription,
""
),
Set(
_Amount,
""
),
Set(
_SpecialPayment,
""
),
Set(
_ImageFile,
""
),
Reset(AttachmentTextInput),
Reset(SpecialPaymentTxtInput),
Reset(AmountTxtInput),
Reset(ItemDescriptionTxtInput),
Reset(InvoiceNumber)
);
Set(
_SelectApprover,
false
);
Set(
_nSelectedApprover,
true
);

[WarrenBelz]

@Big_S ,

Can you please use Format Text and paste all of the in a Text Box (the </> icon above). It should look like the code I posted (indented)

Also can you provide a bit on context as to what you are trying to do with all of that.

I will be offline shortly due to time zone - I will pick up the response in the morning.

[Big_S]

@WarrenBelz  thank you so much for helpin me. 🙂 will try my best also to solve this.

[WarrenBelz · Accepted solution]

@Big_S ,

I spotted a couple of things quickly.

Firstly, get rid of the Concurrent - this will not work if there are any interdependencies in the code. Also for debugging, it is better out.

Secondly you have both Defaults(Collection) and a lookup - do you want a new record of are you patching an existing item? I suspect a new item, it so start your code like this

If(
   _submittingRequest,
   Set(
      _navMenuSelect,
      ""
   );
   If(
      _editingRequest,
      Patch(
         MasterCollection,
         Defaults(MasterCollection)
      ),
      {
         Id: GalleryRequests.Selected.Id,
         Description: ItemDescriptionTxtInput.Text,
         Team: TeamTextInput.Text,
         Type: TypeOfRequestDropDown.SelectedText.Value,
         Approver: Office365Users.UserProfile(Label1_10.Text).Mail,
         ...........................
      }
   )
);

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.