Hi,

I am looking how to auto enter some information in a field upon whats entered in a specific field. Here is the example:

if field "Kursdatum" (date) conatins "24/4 - 4/5 2007" I want the field "Kurs" (course) to be set to "1"

and

if field "Kursdatum" (date) conatins "4/5 - 18/5 2007" I want the field "Kurs" (course) to be set to "2"

and so forth. Probably simple, but I don't find the way.

what is the significance of your range of dates? You probably don't want to hard code them like you are showing, instead you would probably want to use real date fields, or possibly global, depending on your answer to the dates.

In other words, what are you trying to do, besides have a second field reflect 1, 2, etc.a

Lee

Edited March 3, 200718 yr by Guest
add to the post

I don't think I can do that as this is imported directly from MySQL and the dates comes like this.

But please excuse me as I am not a Filemaker programmer so I'm not really sure what you mean also.

Is ther no easy calculation or lookup that can recognize this dates and if so insert a number into the other field?

Hi

auto-enter calculation for Kurs:

Case(

Kursdatum < Date ( 5 ; 4 ; 2007 ) ; 1;

Kursdatum < Date ( 5 ; 18 ; 2007 ) ; 2;

... and so on

)

There is still something I am mising.

I tried to paste the above code streight in to the message field, and got lots of errors.

There must be a tutorial that I could learn the basics from?

I wish you haven't pasted: ... and so on !!

Try this only:

Case(

Kursdatum < Date ( 5 ; 4 ; 2007 ) ; 1;

Kursdatum < Date ( 5 ; 18 ; 2007 ) ; 2

)

There must be a tutorial that I could learn the basics from?

Yes, push F1 :P

Your dates are showing as

Day, Month, Year.

If that is your system date, the the case statement given is showing the date as

Month, Day, year.

All you need to do is to reverse them in your calculation field, and see that does what you want.

As to what I asked.

You haven't explained why you want the 1, or 2.

What are the reasons for the range of dates. The are not a specific timing. 7 days, 14 days, etc.

Once you respond to that then I will suggest an approach so that it will be flexible, other wise unless your need is only until May 18, 2007, you will have to change your calculation for the different periods.

Lee

On the Mac, it is Command ?

All you need to do is to reverse them in your calculation field

:P

Mats, what Lee is getting at is the date ranges mean something. Datum, Course ...

24/4 - 4/5 - set to "1"

4/5 - 18/5 - set to "2"

When you hard-code dates within a calculation, you limit yourself. Next year, are you going to open this calc and change it to 24/4 - 4/5 2008? If you use another table to track your Subject/Courses, you won't need to hard-code it at all.

Oh. And using the Date() function grabs the proper month/day/year according to system date; there should be no need to reverse them. Daniele's calc should work fine.

Edited March 4, 200718 yr by Guest
Added Oh

Oh. And using the Date() function grabs the proper month/day/year according to system date; there should be no need to reverse them. Daniele's calc should work fine.

I don't think so. The date is 24/4/2007 and 18/5/2007.

Lee

I don't think so. The date is 24/4/2007 and 18/5/2007.

Lee, dates are dates :P

You know what. When you guys jumped in, I missed the post about MySQL.

I though we were dealing with a text file.

Thanks for pointing this out to me a couple of times. LOL

Lee

After a good night's sleep I have much to read

Yes the dates are in the right order: (from) dd/mm (to) dd/mm -yyyy and they are 2 weeks coursees for a bartender school and the dates are taked directly from a MySQL database with dates set like this.

I don't know about MySQL, but in Filemaker "24/4 - 4/5 2007" is NOT a valid date. It should be imported into a Text field, instead of a Date field.

Then I suppose you could parse this data out to individual StartDate and EndDate fields, but I still don't know what's the purpose of this calculation. I suspect LaRetta is right, and that you should have a separate table for Courses, and the imported data should lookup from this table.

BTW, "24/4 - 4/5 2007" is only 10 days.

Please forget about the format about the dates. See it as numbers as this is the format / dates I get and have to use. So lets not consider the field as a "Date" field then :)

It's not a number either - it's just a meaningless string of text. If you want to associate it with a specific course, you need to either turn it into something meaningful, or produce a matching meaningless text string on the other side.

A little more background about the workflow would be useful here. What is this data that you get? Where is the information about courses stored? Etc...

Please forget about the format about the dates

Glad we got that cleared up.

As comment has stated, what you have right now is a string of text.

Attach a copy of your raw data, and/or you FileMaker File.

Since this is a forum, with a world wide readership, you should protect any confidential information.

Lee

Hey,

Please. There is no need to be rude and say that it's meaningless numbers / text. A child can understand that that is a date range. That it don't make sense in programming language may be another thing.

I will leave this forum now as the only answers I get is a discussion on how to wright dates, and not an answer to my question. I did not or can not change the dates as this is how I get them when I download them from a MySQL database. It's simple. The answer can be two:

1. If the dates is written in this way it's not possible

2. This is the formula (even if the field is "date123")

No one is being rude. We are discussing Filemaker here. A child can understand that that is a date range - but Filemaker cannot. To Filemaker, it has no meaning.

You CAN change that, if you extract the information in the string into a date field (or two date fields). Then it will have some meaning to Filemaker, for example it will become clear which date is earlier than another.

I did not answer your question as asked, because it was not clear to me - and it still isn't. You gave two examples of input:

"24/4 - 4/5 2007" - (10 days), supposed to produce a result of "1";

"4/5 - 18/5 2007" - (14 days), supposed to produce a result of "2".

Then you said "and so forth". There is no "and so forth" that I can see here. What is the next range after that? How many possible ranges? Until when?

You have been asked, several times, to explain the underlying logic. You anger is misplaced.

Relax Mats,

The discussion of date format was between myself and two other members. Since I do not know anything about mysql, I stopped posting because the bickering was not helping you beyond the calculation already posted, so I ceased posting and was waiting for you to come back and tell us that the calculation wasn't working.

Yes, you can make Text a data. The problem is, we need to know more about the text strings. If we were seeing the MYSQL file you are looking at, we could post something that would work.

In fact, I have a calculation that will deal with that format you gave, but the data needs to be isolated in a field already.

HTH

Lee

MySQL is just fields that anything you programmed will go into the field. I download as .txt or .cvs file and import into FM that I have created a file in with the exakt same field Names as MySQL.

So the date range in FM is now a text field as it's not a valid date field in FM.

if field "Kursdatum" (date) conatins "24/4 - 4/5 2007"

Nobody was being rude ... but neither can we help you without asking questions if you haven't provided enough information for us to conjure a solution.

When you post here, we are blind to what you have and can only make suggestions depending upon what you tell us. It appears to me that it IS a valid date range (International settings). But still, FM would need to be told whether the 24/4 is the same year as the 4/5. We could use calculation to determine whether the day/month is greater than the end string. But neither are you answering questions asked of you and that makes it difficult to assist.

You are speaking with some of the finest people I know. Relax and go with the flow - their only purpose is to help you! Don't you think they'all have better things to do with their time? You should also realize you'd be paying a LOT to hire someone to get what we'all give for free (if not jumped on). C'mon. :wink2:

"24/4 - 4/5 2007" - (10 days), supposed to produce a result of "1";

"4/5 - 18/5 2007" - (14 days), supposed to produce a result of "2" and so on ...

The problem is that this does NOT produce a pattern if the first example produces 10 days and the second produces 14. What is the next range that the calculation needs to produce 3? You see the confusion on our end? For instance, if the next range was 18 days, we could predict 3 by jumping 4 days. The reason we want to understand is because we can't just GIVE a calculation if all you give us is date123. How can we provide a calc for that? We need patterns here to understand the 'and so on.'

We also, because we have more experience using FM, probably can help you in ways you can't even see. Just as I know nothing about MySQL and would take your advice over my own about it. :wink2:

If I would ask the people who made the date range in MySQL to change it to work better with my FM integration of date range.

How would an acceptable date range look like in FM? (The date range has to be in one field and can not be in two.)

At this point, we can't even tell you what to tell the SQL people to do with the data so that you can tell what format they would need to change it to. If you can't post a copy of the RAW data (this is the text (.cvs) file, we might be here a long time working you through to a solution.

I think I'm understanding your problem, short of posting a copy of your files and data, maybe if you just copy and paste the data from the one field you are wanting to make the range out of for now.?

Lee

How would an acceptable date range look like in FM? (The date range has to be in one field and can not be in two.)

There is no such data type in Filemaker, so you cannot solve this on the MySQL end. But as I mentioned earlier, you CAN add two calculation fields, with result type set to Date, that will automatically calculate the StartDate and the EndDate from the imported text string.

That said, it WOULD be easier to parse out the dates if the format were, for example:

"20070424 20070504"

instead of:

"24/4 - 4/5 2007"

Otherwise the calculation can get pretty complex, considering that the two dates may not be in the same year.

So that is a problem then as the RAW data from MySQL is ONE field (Named Date) with the date range as I described in that field. "4/5 - 18/5 2007"

So I have created the same field in FM so that the import from MySQL will work.

And maybe there is a way to make FM separate the dates and put them in to two fields. But that seems even more complicated.

I think it's easier and faster for me to manually set the corse 1-4 (it's four corses) range manually one by one.

The problem is that you can import the raw data into Filemaker, but you cannot work with it - at least not directly - because to Filemaker it is meaningless.

And maybe there is a way to make FM separate the dates and put them in to two fields. But that seems even more complicated.

Yes, that's exactly what I am talking about. It is not VERY complicated, but it is not really simple either. It would be much simpler if the raw data had a year for both ends of the range.

I think it's easier and faster for me to manually set the corse 1-4 (it's four corses) range manually one by one.

Possibly. It depends on what you plan to do with it. It's difficult to advise without knowing your ultimate purpose.

If it helps I can ask them to put "4/5/2007 - 18/5/2007" or what ever you suggest. But still it will be only in ONE field.

Yes, that would be MUCH easier - see the attached. But we still don't know the purpose. What is this data that you are importing, and what do you want to do with it? To continue what LaRetta said earlier: you might be asking how to swim, when there's a bridge nearby.

ParseDates.fp7.zip

Thank you very much. I think that can be arranged.

The purpose is to make my administration easier and call the first corse No. 1, second corse No. 2 etc.

So if the first date range (corse) is: 23/4/2007 - 7/5 2007 I want a field that recognize this as corse No. 1 and so on for the other corses.

Cool. We can then identify the dates as dates. But Mat, this isn't helping us with your original request; hang in there now ... so far, you've given us three dates wanting the following results:

24/4 - 4/5 = 1

4/5 - 18/5 = 2

23/4 - 7/5 = 1

But that's not a pattern for us to determine the logic to plug all date ranges into one of 4 slots. When you look at the dates, how do you determine whether to put a range into 1, 2, 3 or 4? Can you give us this, Start being the minimum date and Stop being the maximum date?

corse 1: Start ___ and Stop ___

corse 2: Start ___ and Stop ___

corse 3: Start ___ and Stop ___

corse 4: Start ___ and Stop ___

Simply; if you can do it manually then *FM can do it much more quickly. But WE can't because we don't know the logic of it yet.

LaRetta

* Okay, a computer can't do everything the human mind can and can't necessarily do it quicker either. But in most cases, it can.

Edited March 9, 200718 yr by Guest
Corrected date

Hi LaRetta,

I am not sure I understand your question. But here is the true 3 dates and courses I am looking to automate for the moment:

corse 1: start 23/4 and 7/5 stop

corse 2: start 4/5 and 18/5 stop

corse 3: start 18/6 and 2/7 Stop

(I did not get your reply emailed to me? so thats why my answer is late)

Then you said "and so forth". There is no "and so forth" that I can see here. What is the next range after that? How many possible ranges? Until when?

You were asked repeatededly to explain the 'and so forth.'

I think it's easier and faster for me to manually [color:blue]set the corse 1-4 (it's four corses) range manually one by one.

You wonder why we kept asking what the other two ranges were when you only gave us the first two? Now you say there are only three. Okay, 3. You were also asked several times about the purpose of it; what you plan to DO with this information (what is the administration work you need to do with it). We have no idea whether this will be a one-time thing or an on-going process; we have no idea if there will be dates at all BEFORE this first date or AFTER the last dates; or whether it will be repeated next year (thus we can’t hard-code the year within the calc). We don’t know whether the ranges will ever change (do they always start on a weekday?), ie, will the start date change next year? We don’t know whether this information will then be used in other ways. We know the questions to ask you and we have … but you have not answered. It appears that the corses can also overlap; correct?

Once you have the year in the starting date; once you have extracted that starting date (per Comment's file), maybe you should just perform a Find on the cStartDate and put 4/5/2007. Then with this found set, put your cursor in the CorseNumber field and type a 1. But we don't know if SOME of the records, instead of starting on 23/4 actually start on 24/4 … are these records individual ATTENDEE records? I would think so, because otherwise you would only have 3 records to import, right? Since they probably are attendance records, will the dates change within the ranges, ie, an attendee misses the first day so the range starts on 24/4? Using a relationship would probably be the best approach but we don't know why you need this so we can't even make a suggestion in that direction!

Mats, every one of your posts have been very short 3-4 sentences and you still don’t answer our questions. With answers to all our questions, we could have had this resolved by now. As it is, we're barely on first base and I, for one, am running out of energy attempting to guess ... and that takes a LOT!

LaRetta

Edited March 9, 200718 yr by Guest

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!

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, 200718 yr by Guest
Added Update and another sentence

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.

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!

... 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

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.

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:

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 :)

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?

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, 200718 yr by Guest

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

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, 200718 yr by Guest
Added another sentence.

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

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

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