How to search using Incident ID and date of Incident

(0) Share

Report

Posted on by rodzmaz81

202

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 . Working Formula.PNG When I add id and date and time formula it does not work.PNG

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (3)

CU-18081211-6 9,270 Moderator on at

Like (0)

Report

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 🙂

Was this reply helpful? Yes No
Verified answer

PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
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#M71518

Best regards,

Was this reply helpful? Yes No
rodzmaz81 202 on at

Like (0)

Report

Hi

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

Was this reply helpful? Yes No
PowerAddict 7,316 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report
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,

Was this reply helpful? Yes No
rodzmaz81 202 on at

Like (0)

Report

Hi

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

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
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!

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report
Hi @rmaziwis ,

I agree with @yashag2255 's thought almost. When you type '2' in the Search box, the Incident number column and the date column both contains the '2'.

On your side, please consider modify your formula as below (using Filter function rather than Search function😞

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

Please consider take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No