Counting missing numbers

[Enigma20xx]

First of all I apologise because I think I’ve seen something similar in the forum but I can’t find it.

I have two tables

Subjects

Exam questions

In subjects table I set the number of lessons, i.e. 8.

In the exam questions table I have a lesson field were to put its number, say 1, 5, 6 and 8.

I have a graphic that shows how many times a lesson have been asked, that works ok.

What I’d like to accomplish is to also know what lessons haven’t been asked.

In the given example would be 2 to 4 and 7.

Thanks to all.

[comment]

The arrangement is not quite clear. In your example, are there 4 related records in the ExamQuestions table?

[Enigma20xx]

I've made a sample file. I hope it helps.

I can make a graph to see the lessons asked, but what I also want is to see (not in the graph, of course), the missing lessons.

In the sample file (2 to 4 and 7).

Thanks comment. Always you. :thumbsup:

CountingLessons.zip

[comment]

I see (hopefully). Shouldn't there also be a table of Lessons?

[Enigma20xx]

I don't think I need one.

I know a subject can have many lessons, and a lesson can be in many subjects (so join table) but, lesson will be just a number, so... I don't know.

[comment]

a lesson can be in many subjects

I think not - unless I have misunderstood completely. The question is, do you have anything to say about a lesson as such - for example:

History:

• Lesson 1: Stone Age

• Lesson 2: Egypt

• Lesson 3: Greece

• Lesson 4: Rome

...

[Enigma20xx]

I'm sorry comment but I don't get what you are trying to say.

I know the db could be very simple as it is and you think in future problems but I can't see it.

How I work with it:

Once subjects have been created I get the exams and populate each subject/question/lesson in the exams table.

Through a script I make a search by suject in exams layout (sorted by lesson/question) and then save it to pdf.

I know it is simple but it's the only way I can do it by the moment.

If you see my idea is going to crash any time while implementing the db, you know your help is very much apreciated.

I made some changes in the sample file so now is nearly the same (simplified) as the real one.

Thanks for your help.

CountingLessonsB.zip

[comment]

I am trying to say two things:

If a subject - say History - has 8 lessons and those lessons are not listed in a Lessons table, then you have no place to keep the name of the lesson, or how difficult it is, or anything else to describe it. That's not necessarily a fault; perhaps you don't need those things.

The second thing is that without such table, you are asking how to list something that does not exist. That's not to say it's not possible - it's just easier to list things you already have. Here we need to generate the numbers 2, 3, 4 and 7, given the list of used numbers (1, 5, 6 and 8) and the final number in the sequence (8).

Since you have the Advanced version, you could use a custom function for this - see the attached.

CountingLessonsCF.zip

[Enigma20xx]

No words my dear friend.

Your first thing. I understand now what you were trying to tell me, I can see the point now. As many information about the lessons as you can have seems to be better (I totally agree with that), but in this case, my db is made just to see which of those lessons have been asked, with no other worries. I can tell you all of them are pretty hard, .

Your second. So the clear thing is that with the table you suggested would have been easier to acomplish my goal. I understand it but I work with the already made exams. Collecting past asked questions I try to predict the future, , so lessons never asked don't mind in this case. Well, they mind but not as much as to make single records of them.

The only Advanced here is my FileMaker version, its "driver" is only a bad beginner.

As said at the begining, no words my dear friend. Thank you very much.

[Enigma20xx]

I've added a field in examquestion table:

cMissingLessonsComa: Substitute ( Subjects::cMissingNumbers ; ¶ ; ", " )

Just to use the result as normal text. i.e. Lessons never asked: 2, 3, 4, 7.

[comment]

You could have made the original field, cMissingNumbers =

Substitute ( MissingNumbers ( List ( ExamQuestions::Lesson ) ; Lessons ) ; ¶ ; ", " )

and place directly it on your layout.

[Enigma20xx]

Is it possible to do the same but with a giving start number?

I.e.,

maxNumber: 55.

lessons of second quarter start: 22.

[comment]

Like this?

CountingLessonsCF+.zip

[Enigma20xx]

No words comment.

Thank you very very much.