Need help with calculating preceding Monday in FMP 16

[djeans]

I have a script to find records by date, and would like to just include records from the current week, starting on Monday. Meaning, if today is Wednesday, I would like it to include records for the current week Monday - Wednesday.

I searched online, and while I did find 1 page that discussed this very thing, I could not make it work.

Their suggestion was:

Div ( date - 1 ; 7 ) * 7 + 1

which I presume the date refers to a date field?

I added it to my script as:

 

Div ( Get ( CurrentDate ) - 1 ; 7 ) * 7 + 1 & "..." & Get ( CurrentDate )

But that returned a number instead of a date (737696...10/4/2020) which failed.

Can anyone help me out with this?

 

[comment]

Try setting the field to:

GetAsDate ( Div ( Get ( CurrentDate ) - 1 ; 7 ) * 7 + 1 ) & "..." & Get ( CurrentDate )

 

[djeans]

That worked Beautifully. Thanks!

[djeans]

I spoke too soon. It looked like it would work (I put a pause/resume step in to see what it entered in the date field) and the correct range was entered, however when the search happens it just returns all records.

During the pause, the range in the date field is 10/5/2020...10/5/2020

but when I resume, it changes to 10/5/2020 *:*:*...10/5/2020 *:*:* 

which then just returns all records.

What do the colons and asterisks mean?

 

[comment]

It looks like the searched field is a timestamp field, not a date field. Still, it wouldn't explain why all records are found.

 

[djeans]

Ahhh. you are right, it is a time stamp field. Didn't think that would make a difference.

When I manually type in 10/5/2020...10/5/2020 the search happens like I expected it to.

Is there a different approach I should take to search a range in a time stamp field?

 

[comment]

2 minutes ago, djeans said:

When I manually type in 10/5/2020...10/5/2020 the search happens like I expected it to.

I believe that if you do that, and then select Records > Modify Last Find, you will see that the hours, minutes and seconds were automatically filled for you with wild cards - same as in your scripted search.

 

[djeans]

Nevermind. I figured it out. 

I had an extend found set step after it that was written wrong. Dumb mistake.