Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

(0) ShareShare
ReportReport
Posted on by 94

Hi 

The accounts team have asked if there is away of generating a list of users and filtering them by their default Category they have against their worker records. This information is located on the Workers record when you go to the Workers section select the user then click on the Project option on the menu ribbon and then Project Setup -

pastedimage1676035282797v1.png

pastedimage1676035392596v2.png

I thought I could accomplish this with the Advanced filtering on the Workers list by joining the correct table up then filtering via Default Category. Unfortunately after trying every Resource table I could find and various timesheet related tables joining them against Worker I have been unsuccessful at locating the table this data is held in.

Does any one know the table this is held on or have a good way of figuring out which tables you need when using the advanced filtering?

Cheers;

Median

  • Median Profile Picture
    Median 94 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    No problem, at least with your help I have a way forward just means setting up the filtering per entity.

    Thank you again for all your help saved the day again.

    Cheers;

    Median

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Yes, You are right. Resource table is company specific, Which means it will not save all data in single go.

    So adding employment table with legal entity wont work here.

    Thanks,

    Girish S.

  • Median Profile Picture
    Median 94 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Hi Girish

    Thanks for your response, the saving query part will definitely come in handy as I think I need to set this up per entity as I had another go at this, but it definitely becomes entity specific when you link the Resources and ResResourceIdentifier joins to Worker.

    I tried to join the Legal entities on employment but due to it already becoming entity specific after the above it joined up this filter will only work on the entity I have select on the top right.

    If I remove the  Resources and ResResourceIdentifier joins then I can search across all entities again.

    Cheers;

    Median

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    You can save the new query also. On the advance filter form - You will have Modify lookup button - After adding the query you can click on the Modify button and click Save as to save the personalized query.

    Also there will be option to select standard query previous to Modify lookup button.

    Refer to the below screenshot.

    18453.Capture.PNG

    To get the data for all the employees - On the Join forms window - Select Employment table - Add "Legal entities as a join - Now try to add the company name from legal entity table as a range and check.

    Thanks,

    Girish S.

  • Median Profile Picture
    Median 94 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Thanks Girish thats definitely got me closer I can filter by what I wanted now I believe unfortunately something on one of the joins must be entity specific so it only pulls through the workers for the entity I have set on the top right.

    Then the issue with that is when you swap entities the advanced filtering doesn't persist so I have to reconfigure it all per entitiy.

    Its a bit off as the Workers section normally lets you search across the board. I will have a play about with it next week and come back to you.

    Cheers for the help so far.

    Median

  • Verified answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Do one thing - On the ResourceSetup(Resource) - Select add table join - Add ProjCategory table to it.

    Now add the ProjCategory table as a range and add a value to it and check.

    Thanks,

    Girish S.

  • Median Profile Picture
    Median 94 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Hi Girish

    I should of said in my last post I had tried that already but it doesn't hold the Default Category info it only holds No or Yes - 

    pastedimage1676040102102v1.png

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    On the range field lookup category is nothing but default category.

    First field in the lookup.

    Try to add that field as range and check.

    Thanks,

    Girish S.

  • Median Profile Picture
    Median 94 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Hi Girish

    Thanks for your response.

    I have followed your guidance and unfortunately I dont have the Default Category which holds the Billable and Non-Billable info. Below are screen shots of each step so you can see what I did :

    resources.jpg

    ResResourceIdentifier.png

    1464.resource.png

    Finished Joins - 

    finished.png

    When I then go to go the Range setup I only have these columns in the Resource table - 

    pastedimage1676039128545v1.png

    Any ideas?

    Cheers;

    Median

  • Verified answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: Table needed to use Advanced filtering to filter workers via Default Category in Resources under Project setup

    Hi Median,

    You can join table like below.

    Select the worker - Click on add join - Select WrkCtrTable.

    Select WrkCtrTable - Click on add join - Select ResResourceIdentifier table.

    Select ResResourceIdentifier  - Click on add join - Select ResourceSetup table.

    Resource setup table will have a field default category.

    After that you can add the category as a  range and filter as you want.

    Refer to the below image.

    1641.Capture3.PNG

    Thanks,

    Girish S.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans