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
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

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

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (80,275)