Goal: I want to create a report that displays all activities from the marketing department.
The activities are stored on the custom object called "Activity". Unfortunately, on the object directly I yet don't have a field that shows the department it is associated with. However, I do have a Lookup(User, Group) field called "Owner". The department is stored on the User object. That's why I tried to add the Department field of the user object using the "add field via lookup" of Activity Object on the report type to then filter by the marketing department. This was not possible since I could not select the Department field once I clicked on the lookup "Owner".
Is there any chance that I could create an additional field on Activity for example a checkbox field (turns true once Owner is from the marketing department) or text field with the Owner's department information?
Note: I can only add a field and not change the existing Owner field. I might have the possibility to use a flow.
Thank you very much in advance :)