Re: Compare rows of data in same table

I'm going to assume you'd be looking at these values for either the Current and Prior years or the Prior and Prior -1 years. Based on that, on the Item Parent, I would first create 2 Formula Numeric fields that would represent the 2 years you are looking to compare. Then, take these values down as Lookups from the Parent down to the Item-Year record.

Next, you can go one of two ways:
Option 1:

• Create 2 Summary fields - one for each of the 2 years - that would be a Max on the Record ID# where [Year Lookup field] = [Year].
• Create 2 new Relationships from the Item to the Item Year record, with the Item Year record being the Parent and using the Max Record ID# from the previous step as the Reference field.
• In each relationship, create Lookup values for each of the fields you would like to compare

Option 2:

• Create Summary fields for each of the fields you would like to compare where [Year 1 Lookup field] = [Year].
• Create Summary fields for each of the fields you would like to compare where [Year 2 Lookup field] = [Year].

They both have their pros and cons, but really, either way should work for what you're trying to do.

------------------------------
Blake Harrison
[email protected]
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------