Formula Help - Next action
Hi, I am new to QuickBase and I am using the standard Sales Management Application but I have created a two new fields in the Activities form, that are "Next Action Date" and "Next Action".
How could I do it so that on the company page, it would show in a field what the "next action is/date" as per the last Activity?
I am hoping this is possible - I am guessing by the formula, but would like some advise please.
Thanks,
I'm guessing that Company has many Activities...
This is going to get a little complicated, because to look up the Next Action, you have to create a many to many relationship, i.e. Activity has many Companies, too.
First:
In the Company<Activities relationship, create a summary field that shows the minimum of the Next Action Date. You will also probably want to filter for Open/Not Complete Actions, so your field is the minimum (next most recent), not completed action date. See pic below for an example.
Second:
Create another summary field in Companies, which is the Minimum Activity ID where [Company - Next Action Date] is equal to the date in field [Next Action Date] in Summary Options like the above example.
Third:
Create a new relationship, Activity has many Companies, with Minimum Activity ID as the Reference Field and add Next Action as a lookup field.
So now you have Next Action Date and Activity - Next Action in your company field.
The *only* drawback is if you have multiple Activities with the same Next Action Date. The first one created (minimum record ID) will be the only one that shows up.
