Auto enter in field

[Mats]

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.

[Lee Smith]

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, 2007 by Guest
add to the post

[Mats]

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?

[Raybaudi]

Hi

auto-enter calculation for Kurs:

Case(

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

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

... and so on

)

[Mats]

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?

[Raybaudi]

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

[Lee Smith]

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

[Lee Smith]

On the Mac, it is Command ?

[Raybaudi]

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

:P

[LaRetta]

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, 2007 by Guest
Added Oh

[Lee Smith]

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

[Raybaudi]

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

Lee, dates are dates :P

[Lee Smith]

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

[Mats]

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.

[comment]

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.

[Mats]

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

[comment]

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

[Lee Smith]

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

[Mats]

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

[comment]

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.

[Lee Smith]

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

[Mats]

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.

[LaRetta]

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:

[LaRetta]

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

[Mats]

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

[Lee Smith]

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

[comment]

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.

[Mats]

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.

[comment]

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.

[Mats]

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.

[comment]

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

[Mats]

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.

[LaRetta]

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, 2007 by Guest
Corrected date

[Mats]

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)

[LaRetta]

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, 2007 by Guest