Total how many values appear in two lookup fields

[megcarp77]

I have an "Applications" table that tracks contacts through various stages: expressing interest (TYPE = EOI), applying (TYPE = Application), and other scenarios for a specific session.

I want to determine how many contacts who expressed interest (TYPE = EOI) in a session ultimately applied (TYPE = Application) for the same session. This will help me calculate the conversion rate between expressing interest and applying.

Is there a way to calculate this?

For example, in the provided screenshot, contact records 14 and 15 both expressed interest and applied for the same session. Other contacts may either only apply or only express interest.

In the "Sessions" table, I have created two lookup fields: one for contacts with TYPE = EOI and another for contacts with TYPE = Application. However, I’m having trouble figuring out how to identify which contacts appear in both lists. Is there a simpler method to achieve this?

I have hidden any contact names. 

[TheTimeSavingCo]

Hmm, from the screenshots it doesn't seem like you're storing the past types of a record, i.e. if it was previously EOI and now it's Application, that data isn't stored anywhere, and so this would be the primary challenge

The simplest way to deal with it would probably be handle the Type changes in a different method that allowed storing of past types instead of overwriting them.  Perhaps checkbox fields to indicate each type, or a multiselect field.  This isn't very nice to look at though and would mess with the workflow

The other option would then be to have automations that would trigger whenever a record's type was EOI or Application, and you'd get the automation to store that information in the record somewhere, for example you could have a multiselect field that you update with "EOI" once the Type became EOI, and then you'd add "Application" to it one the Type became Application

Once you have a good historical log of past Types, you can then create a rollup to filter on that data, does that make sense?

[megcarp77]

Hi. The record would not get updated, a new record is added to the table each time as it will have a different unique application name.

[TheTimeSavingCo]

Ah I see!  Hmm, I think you'd need a table where each record represented a single Contact <> Session, and you'd link all their applications for that session to it.  You'd then be able to see which contact expressed interest and then applied for each session, and you'd link these records to the appropriate "Session" record