Limiting Results with the 'List' function?

[MariaAux]

I am way out of my league here but I would like to give it a go...

At the beginning of each new topic (every 3 lessons), I send the parents an email with what their child is studying at the moment (vocab, grammar etc.) I tradionally did this by hand in a spreadsheet, but since moving the curriculm into FM, I want to see if it is possible to export a list with this data to then use it in a mail-merge email. Let me explain.

I have a big curriculum database as follows;

Grade Lesson # Topic Vocab Grammar (...conversation, song, phonics etc.)

Primary 1º 1 Animals cat, dog I have a

Primary 1º 2 Animals hamster, rabbit I have a

Primary 1º 3 Animals mouse, owl I have a

Primary 1º 4 Food apple, banana I like

Primary 1º 5 Food cake, ice-cream I like

Primary 1º 6 Food salad, pizza I like

Primary 2º 1 Insects spider,beetle What's that?

Primary 2º 2 Insects ant, cockroach What's that?

Primary 2º 3 Insects butterfly, dragonfly What's this?

Etc etc...

E.g. I would like to export the curriculum for Lesson #1-3 so that the following Excel list is exported;

Name Grade Lesson # Topic Vocab Grammar

Johnny Primary 1º 1-3 Animals cat, dog, hamster, rabbit, mouse, owl I have a

Peter Primary 1º 1-3 Animals cat, dog, hamster, rabbit, mouse, owl I have a

Sarah Primary 2º 1-3 Insects Spider,beetle,ant, cockroach, butterfly, dragonfly What's this, What's that

Mary Primary 2º 1-3 Insects Spider,beetle,ant, cockroach, butterfly, dragonfly What's this, What's that

The children table is already related to the curriculum Table by grade, so I need to use the 'List' function but I am having issues;

1) I don't know how to use the 'List' function to limit the results to lessons 1-3, as it will list everything in the field

2) Furthermore, I would need to limit the list results to E.g. Primary 1º, and Lessons # 1-3

3) If I could get that far, then how would I omit double entries such as the grammar in the Primary 1º Lesson 1-3 producing

I have a

I have a

I have a

... rather than just producing it once?

4) I am sure I am going to run into other issues, but let's see if I can get this far. Any ideas to get me started would make me eternally grateful.

Thanks so much in advance and have a great day.

[comment]

• If I understand correctly, each row of your export represents a student - so it would be best (if not necessary) to do the export from the Students table (after finding the students taking the relevant lessons.

• Combining the Vocab values will be a huge problem, unless you split them off to a child table of their own. Then you will be able to pull a list of all Vocab values related to the same Topic.

• It looks like Grammar is an attribute of Topic, rather than of any individual lesson. Therefore it belongs to the Topics table - where each topic would have a unique entry recording all its attributes once.

In short, normalize your data before trying to process it.

[MariaAux]

Hi and Thanks for your reply,

Yes, I will be exporting the list from the Students table.

Vocab is already a table on its own related by the lesson number so that is already done.

Grammar is not an attribute of another table, whereby for example, in the 3 lessons of insects, we learn the grammar 'What's this' for the first two lesson and then the next lesson, but still part of the same topic, we would cover 'What's that'. Thus the teacher know what to cover in that lesson, but over the course of the 3-4 lessons of Insects, we cover more than one grammar point.

Thanks so much

[comment]

Grammar is not an attribute of another table, whereby for example, in the 3 lessons of insects, we learn the grammar 'What's this' for the first two lesson and then the next lesson, but still part of the same topic, we would cover 'What's that'.

I see. So a grammar is an attribute of a Lesson, not of a Topic. Still, consider creating a table of Grammars, then assigning each lesson to a grammar:

Students >- Grades -< Lessons >- Grammars

This way, List ( Grammars::Grammar ) when evaluated from Students, will produce the list you are looking for.

Allernatively, you could use a value list to de-dupe the items.

[MariaAux]

Thanks for the reply.

What do you mean by using a value list to 'de-dupe' the items?

Thanks again.

[comment]

A value list is based on the field's index and therefore does not contain duplicate values. If you define a value list using values from Lessons::Grammar, showing only related values starting from Students (or from Grades), you can then use the ValueListItems() function to produce the de-duped list in the Students table.