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

Date Filter and Sort Formula combine

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)

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

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.

View solution in original post

@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.

View solution in original post

7 REPLIES 7
Super User III
Super User III

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.

View solution in original post

Helper V
Helper V

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

@Big_S ,

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

@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
);

 

@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.

Helper V
Helper V

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

@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.

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (70,298)