Cross-Object Reporting - Possible to Compare Values Between Objects as a Filter?

[LWong2]

Hi, 

I am trying to set up a cross-object report, using Company as the primary data source and Deal as the secondary.

We have an "application number" in the Company that tracks the most recent application, and each related Deal record has its own "application number". 

E.g. A company has three associated deals. The "application number" in the three related deals are "app001", "app002", "app003" with "app003" being the most recent. The "application number" in the company, therefore, is "app003".

I'm trying to create a filter to include in the report only deals where company's application number (i.e. most recent) = deal's application number. Doesn't seem possible, but wanted to tap into the community's collective wisdom.

If not possible, then will have to create a query in our data warehouse, which is not accessible to most HS users. Thanks in advance. 

[BukunmiOdetayo]

Hello @LWong2 ,

I haven't fully understood this. However, from what I understand, I can give the following suggestions:

I assume the company's application number is a company property, and the deal association number is a deal property. From the filters you are looking to create for deals, you can only use contact properties to filter contact reports/automation, etc. The same with company, deal, ticket, and other records.

So, you need to create a deal property that will be a replica of the company's application number (most recent) Note that the field type of the replica deal property must be the same as the field type of your company's application number in question, i.e most recent. Use a workflow to copy the company's application number (most recent) to that property you have just created. You can run a test to make sure the automation works, and that the property is populated.

After that, head over to your report section. You should find the replica property you just created in the filters, and you can use it to include the information you need in your reports.

Let me know if this helps!

Kind regards,

Bukunmi