How to search using Incident ID and date of Incident

[rmaziwis]

Hi All

I have a search formula on an app that allows me to search text values when I try to search using incident ID and  a date and time  I get an error .How do I fix this ?I have added one that works and the one the doesn't .

The Main thing here is I want to be able to Search using the ID as well as the date and time .

[gabibalaban]

That is not an error, it is a delegation warning ! It appears because the Datetime data type is not delegable in Sharepoint (yet).

Here you can find more about delegation to SharePoint:

https://docs.microsoft.com/en-us/connectors/sharepointonline/

Check out this post by RandyHayes for more details on how you might be able to get around it using Flow"
https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/Problems-with-delegation-when-filte...

All credits can go to  mdevaney 🙂

[PowerAddict · Accepted solution]

You can use AddColumns function to convert these columns into text and then search by these.

For example,

SortByColumn(Search(AddColumns('[rp].[Form_InjuriesOnDuty], "TextID", Text(ID), "TextDateandtimeofincident", Text(Dateandtimeofincident)), 'txt.InjuriesOnDuty.Text'.Text, "Siteofincident", "StstusOfincident", "COIDclaimnumber", Nameofinjuredperson", "Daysbookedoff", "TextID", "TextDateandtimeofincident"), ID, If(asendingOrder = true, Ascending, Descending))

Please keep in mind that the number of records returned by the AddColumns function is equal to the delegation limit. So if you will have more than 500 records, please increase that number to the max value of 2000.

Here is some more info about AddColimns function:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

Here is how to change the delegation limit:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview#changing-the-limit

Let me know if this works for you and if your number of records will be more than 2000.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[PowerAddict]

Hi @gabibalaban

I dont think what we see in the screenshot is a delegation warning. That's an error because we cannot search by numeric or date columns. We can use the AddColumns function to create text versions of those columns and then search by these new columns.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[v-xida-msft]

Hi @rmaziwis ,

Based on the issue that you mentioned, I think this issue is related to the "Dateandtimeoffincident" column in your data source.

Currently, within PowerApps app, the Search function could only accept Text type column as Search column, you could not specify a date time type column as Search column within the Search function.

As an alternative solution, I think the AddColumns function could achieve your needs. Please consider take a try with the following formula:

Sort(
     Search(
            AddColumns('[rp].[Form InjuriesOnDuty]', "DateAndTimeOfIncident", Text(Dateandtimeofincident)),
            'txt.InjueiesOnduty'.Text,
            "Siteofincident",
            "StatusOfincident",
            "COIDclaimnumber",
            "Nameofinjuredperson",
            "Daysbookedoff",
            "DateAndTimeOfIncident"    // add Text type column here
     ),
     Id,
     If(ascendingOrder, Ascending, Descending)
)

Note: Please consider set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

If the amount of your data source records is more than 2000 (Delegate limit), please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Best regards,

[rmaziwis]

Hi

Iam getting an error maybe you can point me in the right direction .The datasource is correct

[PowerAddict]

Can you share some of the error messages that you are seeing?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit Bhatia
https://thepoweraddict.com

[v-xida-msft · Accepted solution]

Hi @rmaziwis ,

Could you please share more details about the error message?

Based on the formula that you mentioned, I could not find any syntax error with it. According to the screenshot that you mentioned, I think this issue may be related to the column value reference within your Gallery.

Please check if you have specified proper column value reference within your Gallery using the following formula:

ThisItem.Column1

ThisItem.Column2

ThisItem.Column3

...

Best regards,

[rmaziwis]

Hi 

I have got it to work but it still wont search by ID or Date .

[yashag2255]

Hey @rmaziwis 

The issue here is, that the Search function works with the substrings as well, since 2 is available in the dates as well as part of the year, it is returning you the results.

To resolve this, can you try to update the expression to use startsWith function as:

SortByColumns(Filter(AddColumns('[rp].[Form InjuriesOnDutyF]',"TextId",Text(Id),"TextDate and time if incident",Text('Date and time of incident')),StartsWith(Siteofincident, txt.InjuriesOnDuty.Text) || StartsWith(StatusOfincident, txt.InjuriesOnDuty.Text) || StartsWith(COIDclaimnumber, txt.InjuriesOnDuty.Text) || StartsWith(Nameofinjuredperson, txt.InjuriesOnDuty.Text) || StartsWith(Daysbookedoff, txt.InjuriesOnDuty.Text) || StartsWith(TextId, txt.InjuriesOnDuty.Text) || StartsWith('TextDate and time of incident', txt.InjuriesOnDuty.Text)), "Id", If(ascendingOrder=true,Ascending,Descending))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!