Jump to content

How do you calculate a field from a date field?

This topic is 5711 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!


Edited by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 )



Link to comment
Share on other sites

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 )


Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5711 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.