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

(0) Share

Report

Posted on by Median

106

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 -

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

I have the same question (0)

All responses (11)

Answers (2)

Suggested answer

Alireza Eshaghzadeh 14,743 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Median,

Resource Setup is seperate table that has relation to worker table.

I think you can use TableBroswer for this filtrering. You need to add following URL

https://URL for sultion/?mi=SysTableBrowser&TableName=ResourceSetup&cmp=DAT&limitednav=true&lng=en-us

Remember to change DAT to companyID

Example:

Was this reply helpful? Yes No
Verified answer

GirishS 27,829 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Thanks,

Girish S.

Was this reply helpful? Yes No
Median 106 on at

Like (0)

Report
Copy link

Link copied!

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 :

Finished Joins -

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

Any ideas?

Cheers;

Median

Was this reply helpful? Yes No
GirishS 27,829 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Median 106 on at

Like (0)

Report
Copy link

Link copied!

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 -

Was this reply helpful? Yes No
Verified answer

GirishS 27,829 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Median 106 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

GirishS 27,829 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

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.

Was this reply helpful? Yes No
Median 106 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
GirishS 27,829 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No