Finding a max value of a reference list and returning a different value from the row

[Andrew_Hock]

I want to create a function for lead follow up. So, there’s a Table, “Team and Agents” with a row called, “Follow-Up Date.” There is another table called “Followup”, which references to “Team and Agents.” Each time the Owner contacts a lead I want to add a row to “Followup.” With the addition of a new row, a follow-up date is required, called “Next Follow-Up”. The owner can pick 30, 60, or 90 representing the number of days from now to follow up on. In the row, Team and Agents(Follow-Up Date), we want to display the latest submission date + the latest submission date’s “Next Follow-Up.”

I’m having trouble figuring this one out. This is all I have so far and I couldn’t get it to work right. Please help.

(LOOKUP(MAXROW(“Followup”, “Date”, ([Agent] = [_THISROW].[Name])),“Followup”,"_RowNumber",“Date”))
+
LOOKUP(MAXROW(“Followup”, “Date”, ([Agent] = [_THISROW].[Name])),“Followup”,"_RowNumber",“Next Follow-up”)

[Marc_Dillon]

Are you trying to do some sort of Date/Time math here?

Or are you just trying to concatenate 2 date values into 1 text field?

If the former, I think you’ll have to describe your situation in more detail.
Are you having trouble adding the 30/60/90 days to your date?

If the latter, try just changing the ‘+’ in your expression to a ‘&’. Assuming both of those LOOKUP() expressions are returning the correct value separately.

[Andrew_Hock]

Yes, I am trying to add days to a date. The problem is trying to pull the two variables in order to do the addition. The lookup functions are not working correctly.

Best,

[Marc_Dillon]

Do the 2 LOOKUP() expression work correctly by themselves?

Is there any error shown?

Maybe try wrapping the first one inside of DATE(), and the second inside of NUMBER() to force the correct data types.

[Steve]

Marc_Dillon:

Maybe try wrapping the first one inside of DATE(), and the second inside of NUMBER() to force the correct data types.

Yep.

I would actually recommend instead that you add a normal (non-virtual) column to the Followup table with an App formula expression of:

([Date] + [Next Follow-up])

Then you can just query that column directly to get the date.

The expression you’re trying to use is very, very inefficient. You absolutely do not want it in a virtual column if you care about performance.

[Andrew_Hock]

I’m struggling.

(LOOKUP(MAXROW(“Followup”, “Date”, ([Agent] = [_THISROW].[Contact Number])),“Followup”,"_RowNumber",“Next Follow-Up Date”))

This does not return anything.

[Steve]

Have you tried the Test button in Expression Assistant?

[Andrew_Hock]

The test link gets stuck in loading.

[Steve]

Screenshot, please.

[Andrew_Hock]

I was using Safari. Chrome displayed it, and I got everything working. Thanks again. You rock!