Auto enter in field

[Mats]

Sorry for waisting your time. But what have I not explained??? English is not my mother toung, but I tried to keep it to my the simple English I can.

1. I have a Bartenderschool register with 3 corses. (One was just cancelled)

2. I want to have a field named "Course No."

3. If the student have booked the first corse 23/4/2007 to 7/5/2007 the field "Corse No." will be automatically set to "1" as this is the first corse. So thats Corse 1.

4. And thereafter the same for the other corses. Second corse witch is 21/5/2007 to 4/6/2007 will be Corse 2 in the "Corse No." field.

The "Corse No." will make the administration easier in many ways as there are many student's.

After that we had as you said discussions that the first corse date range I used was 10 days and not 14 days as the others. That the date format was wrong and so on.

Except from the date format, I think my question was very simple and straight forward??

Again, I am sorry if there some of your question that I do not understand!

[LaRetta]

This helps greatly! Once you get that text date range with the year then you can run with it! You need a Course table. It would have an Auto-Enter SERIAL starting at 1 called CourseID. Your first record in this table would automatically become Course 1. This course table would also have two dates: StartDate and EndDate - YOU choose what dates go in here as you create each new Course record - make sure they are date fields. You would then have an Attendance table which includes CourseID and StudentID and both of Comment's calculation fields (result of dates). In your relationship graph - ONLY to identify the course numbers in your Attendance records, join from:

Courses::StartDate [color:green]:less: Attendance::cStartDate

AND

Courses::EndDate [color:green]:great: Attendance::cEndDate

Now when you import into your Attendance table (to that text date range), it can automatically look up the course at the same time. So you would do this:

Import [ ... into your text date range field after you have added the year to the beginning date) ]

Replace Field Contents [ No Dialog ; Attendance::CourseID ; Courses::CourseID ]

This will (record by record) evaluate through the relationship to Courses and insert the correct course into each Attendance record.

I don't know if this is on-going or whether you will switch to entering this data directly into FM or whether you will always get the attendance records through import. Either way, if you wish to know who attended which course, you would create another copy of your Attendance table in the graph and join *IT* to Courses on the CourseID on = (don't include the dates in here). In this way, you can get counts of Attendance, view attendees directly on a Course layout (via a portal) and so forth. And later, if you enter attendance records directly in FM, you will want to use the TRUE relationship which is CourseID = CourseID. Once all entered in FM, you won't need to look up the CourseID because FM will insert it for you.

I think we're moving now.

UPDATE: Um, sorry I forgot ... I had meant to change the < and > to include equal to as in: :great: :less: but that meant I had to stop typing and grab the symbols from the side-bar and I was going to go back and do it and I never did. I've changed them now.

Edited March 9, 2007 by Guest
Added Update and another sentence

[comment]

If no two courses ever start on the same day, you could match on the start date alone.

Another option (which I mentioned earlier) is to have a calculation field in the Courses table, whose result is the same text string that comes with the imported registrations. This way there would be no need to change anything in the MySQL DB.

I wonder if all this couldn't be done at the registration stage. After all, the information about the available courses has to come from somewhere, in order to be be presented to the applicants. So why not send it out as:

Course#....From.........Until

1..........23/4/2007....7/5/2007 

2..........21/5/2007....4/6/2007

and the applicant selects course #2 directly.

[Mats]

So finally you understand me

No offence but is there not a simpler way? I liked the template that "comment" send me (ParseDates.fp7.zip) as above.

Looking at that and knowing that all courses are after each other the "Course No." field could be based only from the start Date of the course.

So if I can write some home made up explanation code for what I would find easier, but maybe so you understand what I mean?

Calculation: If course::DateStart = 23/04/07, set course::Course No. = 1

Calculation: If course::DateStart = 21/05/07, set course::Course No. = 2

If you get my point??

Again, no course dates will be at the same time!

[LaRetta]

... which is what Daniele suggested originally (except it was based upon true dates which you will create from Comment's file). No two Course start dates may be the same but I was just going to post this to Comment ...

Yes, of course - not necessarily a need for both ends of the relationship. But I envisioned records from the current year's upcoming Courses overlapping and got concerned in my anal way. It is difficult without seeing the data.

... whose result is the same text string that comes with the imported registrations. This way there would be no need to change anything in the MySQL DB.

But if an attendance record is the day after it starts, matching to a text string will break. In other words, I don't know if there (currently) is ONE boolean set from which a person selects (normalized data) or whether it is a summary produced from MySQL which produces the range based upon days the PERSON attended. There is no current distinction between what the data looks like now vs. how it has been requested to be in the FM version. It is the prior data that concerns me but matching on StartDate should work.

Oh yeahhhh, we're rockin.' Thanks, Michael!

LaRetta

[comment]

Mats said these are REGISTRATIONS, not attendance. I am still missing the big picture here. I sort of imagine that there is a web site somewhere where you can register for these courses, and the results are sent out from there.

But that is only a guess, and I don't have even a guess about what's supposed to happen with this information after it arrives.

[LaRetta]

Or does MySQL produce the registration from attendance records (start/end date per attendee). That's what I'm unsure of. Because Mats said the MySQL people could generate different date-range list meaning they created this one; but from what? The dates given haven't been exact - is the data? Mats, we still have many questions here. That's what we mean by struggling to get a vision of the data and the process. If we can't see either end, we can't solve the middle. :wink2:

[Mats]

I just had access to the Form setup for whats going into the My SQL Database.

You can see on the registration date "Datum" drop down list how they choose the dates. http://www.bartenderskolan.eu/anmalan.php

BUT there is another field for the programmer what He/I want to be inserted also. See picture:

As you can see, the student see's:

Datum:

23/04/07 till 07/05/07

But as you see on the picture to the right I wrote "Kurs 1" (course 1) and that's whats going into the MySQL and later FM. So now as I have access, we also know the options.

(please keep it simple for me guys ) I want the dates & course to show in FM.Preferably Course No. in it's own field.

(Did you see the end now :)

[Mats]

I am trying some of the earlier suggestions and what do I do wrong here?

Case ( Kursdatum < Date ( 23; 4 ; 2007 ) ; 1; )

Case ( Kursdatum < Date ( 21; 5 ; 2007 ) ; 1; ))

Case ( Kursdatum < Date ( 18; 6 ; 2007 ) ; 1; )))

If I use only the start date and the first string "Case ( Kursdatum < Date ( 23; 4 ; 2007 ) ; 1; )" It works fine.

and can I implement this string "LeftWords ( Imported ; 1 )" if I have the date range field set to text? And if so how/where?

[comment]

The thing that you are doing wrong is trying to do this with a calculation. It should be done with a relationship.

In the attached file, there are 2 tables, one for Courses, one for Registrations. When you enter (or import) a registration, the CourseID is immediately shown. It is a field from the related record in Courses. (the number could also be looked up into another field in the Registrations table).

This way you can add an unlimited number of courses, and the solution will keep on working. With a calculation, you would have to change the formula every time you open a new course.

ADDED:

I still don't understand why can't you send the course number directly out of MySQL. The picture you attached is exactly what I meant: let the student select a date range (Item Label), and have the program export the course number (Value). This will both simpler, and more reliable (in case the course dates need to changed for some reason).

ParseDates2.fp7.zip

Edited March 10, 2007 by Guest

[Mats]

Dear "comment",

I did start do understand the samples you send me. I have some minor problem left to get the relationships and some other things getting right.

If you please coud take a look? Please!

The text is in Swedish but I think you will get the most. "Kursdatum" = Course date and "Kursnr." is where I want the number of the course.

Anma__lda_Register.fp7.zip

[LaRetta]

You have no Course table. You need to create it. Make sure it's exactly like Comment's file. Remember the Course IDs must be auto-enter serial (number) starting at 1. That's the number (from Courses) that needs to pull into each Registration record. :wink2:

Edited March 10, 2007 by Guest
Added another sentence.

[LaRetta]

When you create your first record in Courses, that will be Course 1. Plug in the start and end dates in that table.

[Mats]

I have been looking, but did not find how to do it :)

[Mats]

I know but there are other things missing to that I can't find out holw to make. I will try some more first.

[LaRetta]

Create a new table in your file, Mats, called Courses (click the tables tab). Make it look exactly like Comment's file (including the calculations). Then in the graph join just like Comment's file shows:

Courses::StartDate = Anmalda Register:Kursstart

... you have it joined to the end date Kursslut (I think). I'm a bit lost in the language but Kursstart, and the fact that the calculation [color:green]LeftWords ( Import. Datum ; 1 ) is the starting date. You also have Kursstart as data type NUMBER and it must be date.

Look in Comment's file and re-read the posts. I realize it's difficult for you because of language but it's important. There are TWO layouts - one is based upon the Registration records ( your Anmalda Register ) and the other layout is based upon the new Courses table (which you need to create first). If you view the Courses layout, you will see your courses 1, 2, 3 with dates that you TYPE IN. After you type in your 'criteria' in these records, the relationship will produce a MATCH. When the Course Start Date matches records in Registrations (Kursstart calculation DATE), the relationship will be valid. Then place the Courses::CourseID on a Registration layout. Keep working with Comment's file and re-read these last few posts a few times as you go through his file.

LaRetta

Edited March 10, 2007 by Guest
added a bunch more

[Mats]

Thanks for the help every body. It's working like I want now ;)