Skip to main content

Hi friends, I would appreciate your help with something that I have been stuck on for too long..

I have 2 tables -
1. Students
2. Student Actions

In "Student Actions" I see all the lessons that the students have completed.

In "Students" I see the student's status - both his details and I also want to pull the last lesson he took, the date of the last lesson, etc.

I was able to use ROLLUP to pull the date of the last lesson, but no matter what I did and what I tried - I was unable to pull the name of the last lesson he completed.

Now it seems like something very simple to me, because I need to pull the name of the lesson whose "Autonumber" is the highest, regardless of the date.

I was unable to do this and it is really frustrating.

I would really appreciate your help!

Thank you very much!

 

Try:
1. In Students, create a rollup field with 'MAX(values)' to get the latest autonumber
2. In Student Actions, create a lookup field to display the field from step 1
3. In Student Actions, create a formula field to check whether the lookup field's value is equal to that record's autonumber
4. In Students, create a lookup field to display the name of the lesson, and give it a conditional to only display from records where the formula field from step 2 is positive

Link to base

 

Hi,
Approach, described by @TheTimeSavingCo , is a good way to achieve your goal, but it depends on type of  'one to many', 'many to many' connections between tables.
It may not work if the Lesson includes several students with different 'Last lesson'
I solved such problem with additional formula field (in lessons). it mixes autonumber with lesson name, for example:
"[4] - The name of lesson 4 " 
square bracket here chosen as a kind of delimiter. You can choose any char, that were not used in lessons name text.

Then you should add rollup ARRAYJOIN(values) for this field in Students table.
You'l get something like 
"[3] - the Name of third lesson [5] - fitfth lesson's name [4] - the name of lesson four"
You already have number 5 as a number of last lesson. 
So you need to cut the piece, starting from "[5]" up to next "["
There is a possible flaw - if the fifth lesson is last in list, there is no ending "[". Easiest way to fix- use 
 ARRAYJOIN(values) &'[' in rollup formula.

I would use REPLACE and LEFT, but there are several ways to do it.
I think, REGEX_EXTRACT might be more compact and 'better readable'  

without REGEX, it's something like (numbers in '3+' , '-2' should be adjusted)

LEFT(
REPLACE(rollup_field,1,3+FIND('[2]',rollup_field),''),
-2+FIND('[',
REPLACE(rollup_field,1,3+FIND('[2]',rollup_field),'')
)
)

 

Reply


Terms & Conditions