Displaying most recent and next upcoming record

Question

Hello!My app is used to track my team's relationships with teachers, who use our cases as teaching material in their classrooms. My team is interested in seeing how a teacher's experience using cases changes once we offer them teaching advice.&nbsp;For this reason, it would be helpful to be able to see information for the teacher's most recent case before we offered advice, as well as for the next case that they taught.&nbsp;My two relevant tables are&nbsp;Cases and Teaching Advice. 1 Teaching Advice has many Cases.&nbsp;I am halfway there. I've successfully figured out how to pull in information for the teacher's most recent case:&nbsp;1. Summarize the Maximum Case date where the [Case date] is before the date in the field [Teaching Advice Date]. Call this [Most recent case before teaching advice]2. Look that up down to Cases.&nbsp;3. Summarize the maximum [Record ID#] where the [Case date] is equal to the date in the field [Most recent case before teaching advice]. Call this [Record ID# of most recent case before teaching advice]4. Then I created a reverse relationship where 1 Case has many Teaching Advice, based on the reference [Record ID# of most recent case before teaching advice], which summarizes the maximum of Record ID# based on [Case date] is equal to the date in the field [Most recent case before teaching advice]. I added the relevant lookup fields I wanted to see.&nbsp;Then I attempted to duplicate all of these fields, to pull in information for the teacher's following case, with a couple of small changes in italics:&nbsp;5. Summarize the Minimum Case date where the [Case date] is after&nbsp;the date in the field [Teaching Advice Date]. Call this [Case immediately following teaching advice]6. Look that up down to Cases.&nbsp;7. Summarize the minimum&nbsp;[Record ID#] where the [Case date] is equal to the date in the field [Case immediately following teaching advice]. Call this [Record ID# of case immediately following teaching advice]8. Create another reverse relationship where&nbsp;1 Case has many Teaching Advice, based on the reference [Record ID# of case immediately following teaching advice], which summarizes the maximum of Record ID# based on [Case date] is equal to the date in the field [Case immediately following teaching advice].&nbsp;I am running into a problem with step 5, and I can't figure out why. The field won't populate with information. If I change "after" to "before", then the information does fill in, but it's the wrong information -- it's all of the information for the most recent case, not the following case (which makes sense). This makes me think that either I have one small mistake that I'm not seeing, or that Quick Base won't let me do multiple relationships between the same two tables.I'd appreciate your advice! Thank you!------------------------------Laura------------------------------

laurathacker · Answer

I think you could simplify this by doing the following:

1. Your first "summary" field to get your Record ID# to give you the last-Case date that was entered before the Training Advice was given would be setup as follows:

Summarize the MAXIMUM [Record ID#] when [Date of Case] is before the date in the field [Date of Training Advice]

Use this summary-field value as the formula-value in your reverse-relationship to identify the information from the last-case entry before training.

2. Your second Summary field to get your Record ID# to give you the first-Case date after the training advice was given would be setup as follows:

Summarize the MINIMUM [Record ID#] when [Date of Case] is after the date in the field [Date of Training Advice]

Use this summary-field value as the formula-value in your reverse-relationship to identify the information from the first-case AFTER training advice.

Remember that ALL your Cases must be connected to the Training Advice Record. Your workflow description suggested that Cases would be entered before Training Advice was given; and so I wonder what process you have for ensuring that existing Cases are assigned to a new Training Advice entry.

If you pull your summary fields as Record ID#s rather than "dates" you may find this yields more accurate results.

Did you know about the new Summary Combined Text Field? This has solved a lot of need for reverse relationships. You can summarize text data values (unique ones) from child-record data without having to physically build the reverse relationship itself. You will still need the summary fields to determine the Record ID#s to pull the data from; but you do not necessarily need the whole relationship connection. You often then have to convert the summary-combined-text-fields into Formula-Text fields for readability though.

------------------------------
Laura Thacker (IDS)
laura@intelligentdbs.com
(626) 771 0454
------------------------------

lauramcfeely · Answer

Thanks so much, Laura!I like your idea to simplify, but I think your way would rely on cases always being entered chronologically in terms of when they happened, and therefore Record IDs always being in order from smallest (earliest date) to largest (latest date). Unfortunately, we can't rely on that -- we sometimes find out about cases months after they happened, which is why I built it on Date rather than Record ID#.&nbsp;I'm not seeing how your summary field for the first case after receiving advice is different from mine, other than being based on Record ID#. For some reason, summarizing by the minimum [Date of Case] when [Date of Case] is after the date in the field [Date of Teaching Advice] is not working.&nbsp;I did not know about the new Summary Combined Text Field! That's very interesting, and could be really helpful. I'll play around with it and see how I can use it.&nbsp;&nbsp;Thanks again!

laurathacker · Answer

If you would like to jump on a call with me, I will try to help you fix the problem with # 5 of your existing setup then.

------------------------------
Laura Thacker (IDS)
laura@intelligentdbs.com
(626) 771 0454
------------------------------

laurathacker · Answer

I replicated your two date summary fields and got results on both ends; so a possible reason why step 5 is returning en empty result is if the Case is not actually connected to the Training Advice.
------------------------------Laura Thacker (IDS)laura@intelligentdbs.com(626) 771 0454------------------------------

lauramcfeely · Answer

Hi Laura,&nbsp;Thanks so much for taking the time to test this out, and for helping me think this through. After a good night's sleep, I realized that you're right, of course: I need to connect the cases to the training advice, which is the crucial thing. After thinking about this, I think that the complication of connecting them is ultimately not worth the slight increase in convenience.&nbsp;So I'm sorry for wasting your time, but I appreciate that you helped me figure out why it's not worth it!Laura

Forum Discussion

Displaying most recent and next upcoming record

6 Replies

Related Content

Upcoming Customer Events for Spring 2024

Formula - Most Recent Date

Formula to display someone's age based on date?

Reminder Notification for Upcoming Birthdays

Most Recent Record

Recent Discussions

Can I use quickbase actions within the same table?

Question about relationships

Reports & Charts - Managing Client-Specific Name Visibility

Summary Table Question

Google Search is back