Partial Index Match on an identity field

I have set up a dynamic dashboard where users can filter by their name to determine how many documents they are required to complete. On selection of the count a report is opened with the list of documents assigned to them. I am running into an issue on the report when a single document is assigned to multiple people as the report only shows the documents a user is solely responsible for.

I have a Resource page which lists all members (field type Identity) of the team and a helper row that flags the user that is filtered. Formula: =IF([Primary Column]@row = MAX([Primary Column]:[Primary Column]), 1, 0)

In my plan, I have an Owner column that flags if the filtered resource is assigned to the document. Formula: =IFERROR(IF([Resource Name]@row = INDEX({Sprint Resources Resource}, MATCH(true, {Sprint Resources Last Row}, 0)), 1, 0), ""). This is the indicator used to filter the report. All names are have identity field types.

Is it possible to modify this to a partial match so that if Resource A is selected in the filter, all documents assigned to Resource A and Resource B have the Owner flag? Or any other formula that would solve this issue?

I have already tried the below modifications with no luck:

=IFERROR(IF([Resource Name]@row = INDEX({Sprint Resources Resource}, MATCH(true, {Sprint Resources Last Row}, 0)), 1, 0), "")

=IFERROR(IF([Resource Name]@row = (FIND({Sprint Resources Resource}, @cell) > 0), 1, 0), "")

=IFERROR(IF([Resource Name]@row = INDEX((FIND({Sprint Resources Resource}, @cell)>0), MATCH(true, {Sprint Resources Resource}, 0)), 1, 0), "")

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=IF(HAS([FS Owner]@row, INDEX/MATCH), 1)

All comments

Paul Newcome

Are you able to provide screenshots for context? It sounds to me like you are going to need an INDEX/COLLECT instead with a HAS or CONTAINS function, but I want to be sure.

bbates

Of course @Paul Newcome!

Plan where I need to check if the Owner matches the filtered Resource:

The {Sprint Resources Resource} page that lists all resources and the flag that indicates the filtered resource:

The dashboard count showing that the total assigned to the filtered resource is 15 since this is derived from a separate formula:

The report linked to the metric that displays only 8 documents assigned to the filtered resource. The other 7 have multiple resources assigned:

Paul Newcome

Try this:

=IF(HAS([FS Owner]@row, INDEX/MATCH), 1)

bbates

Amazing!! Thank you!!!

Final formula: =IFERROR(IF(HAS([FS Owner]@row, INDEX({Sprint Resources Resource}, MATCH(true, {Sprint Resources Last Row}, 0))), 1, 0), "")

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.