How do you calculate a field from a date field?

[Abaworld]

I see a lot of great posts here, but none that address my issue.

The main "problem" is that I am new to FileMaker (and databases in general) and I have to tackle the reservation, invoicing, etc project created by a former employee. They did not complete the project but it is usable with some limitations/errors.

The first issue:

We have a 'booking number' field that is calculated using the 'arrival date' and first 3 of 'customer's last name'. The 'arrival date' is entered via calendar. Problem is that if the month and/or day is a single digit, the 'booking number' field will have slashes inserted in place of the number. If I setup the calendar to insert leading zeroes, they appear in the field, but not in the calculated 'booking number'. However, they do appear in the 'booking number' if the date is entered manually. The easy fix would be to enter the date manually instead of using a calendar, but I would prefer an easier and less likely to error method.

These are the two fields used for the calculation:

'booking number' = (trim booking number)&Left(client Last name;3)

'trim booking number' = Middle ( arrival date ; 1 ;2)&Middle (arrival date ;4 ;2 )&Middle ( arrival date ; 9 ; 2 )

I have tried a number of different functions in place of "middle" without success.

There is a "part 2" to this that I have absolutely no clue:

Sometimes, the booking requires a one-way departure, so no entry in the 'arrival date'. My fix is to enter the 'departure date' in the 'arrival date' field so that the booking number can be calculated. Is there a way to have the program look into the 'departure date' if the 'arrival date' field is empty and then use that date for the 'booking number' calculation?

I appreciate any help provided and will, very likely, have a few (probably many...) more questions. I hope that my explanation is understandable.

Thank you!

Pete

Edited February 10, 2007 by Guest

[jamesducker]

I'm assuming you'd want the booking reference for a booking arriving today (10th Feb) by me (surname Ducker) to be "070210DUC".

I would say the 'booking reference' field calculation should be:

right(year(arrival date);2) &

if(month(arrival date)


The first line of that LET statement decides which date to use... if the arrival date is empty, it sets a "variable" called $date to the departure date; otherwise it sets $date to the arrival date. The calculation to work out the booking number is then performed on the contents of the variable, rather than on the arrival date as in the first example.


Note that "variables" defined in LET statements aren't available for use anywhere other than in the 'specify calculation' box you define it in. (They don't have to have a leading $ either, but I always do that to avoid confusion with field names.)

[Abaworld]

Thank you, jamesducker, for the response!

Actually, the current format is mmddyy***, but I can see valid reasons for having the year, first. Thanks for that hint.

Now, let's see if I can get it to work...

[jamesducker]

My pleasure :-)

Dates in the format YY-MM-DD will always be in date order when sorted alphabetically.

[Lee Smith]

Lets start with the date. I'm not sure I understand what you want from the date, but you could use the number that is stored by FileMaker for the date.

Left( ClientName; 3) & GetAsNumber ( arrival_date )

However, if you want the date to be like 02/10/2007 instead, it would be

Left( ClientName; 3) &

LeftWords ( Substitute ( arrival_date ; "/" ; "|" ) ; 1 ) & MiddleWords ( Substitute ( arrival_date ; "/" ; "|" ) ; 2 ; 1 ) & MiddleWords ( Substitute ( arrival_date ; "/" ; "|" ) ; 3 ; 1 )

HTH

Lee

[Abaworld]

It worked perfectly!!!

Thank you, again!

Pete

[Lee Smith]

I see you already have an answer, but I thought you might like to see a Let Function on this two.

Let (

date = Substitute ( arrival_date ; "/" ; "|" );

Left( ClientName; 3) & LeftWords ( Date ; 1 ) & MiddleWords ( date ; 2 ; 1 ) & RightWords ( date ; 1 )

)

[jamesducker]

Of course ;-)

Next question?

J

[Abaworld]

Thanks, Lee. jamesducker's solution did everything that I needed, but I will store your suggestion away for future possibilities.

Pete

[Abaworld]

Of course ---- Hmmm, you must be British... :

Since you asked -- next question is:

Can FM prevent a form with critical info, being mistakenly emailed?

example: reservation form has credit card info, invoice confirmation does not. Extreme possibility for reservation to be emailed instead of invoice (been done already...). I want to make it ***impossible*** to send the reservation form.

Pete

[jamesducker]

British indeed, guilty as charged ;-)

Are you talking about 'save records as PDF' with the 'attach to email' option switched on, and the possibility of that command being used to PDF and send the wrong layout?

[Abaworld]

Yes, as well as the option to attach pdf to send mail. I would prefer to not save the invoice confirmation as a pdf - just send it via FM, but, alas, I haven't figured out to do that...

Your response initiated the thought that there must be other ways to send the email. So, now I have discovered scripting. Digging in to find out what I don't know (quite a bit...) after dinner.

Edited February 11, 2007 by Guest

[jamesducker]

Excellent. Save as PDF + email is probably the best way. I'd suggest you automate it with a simple (one-line) script, attach that script to a button and put the button on the right layout. If the users have a button to press, and can only press it from the right layout (make sure the script isn't shown in the scripts menu), you should be fine.

To be sure the other layout can NEVER EVER EVER EVER be saved as a PDF, you'll need to use FileMaker advanced to create a custom menu set which removes the save as PDF option from the file menu, and prevent the users from reverting to the standard menu set or using ScriptMaker.

But the first step alone should stop genuine mistakes.

I'm off to bed. Enjoy your dinner! Let us know how you get on.