I though this would be easy, but I can't find a way to get(currentDate) for just the month?
Did you set your calculationresult to number?
Because when it's only the month the result isn't a date but a number.
You can use:
Month (Get ( CurrentDate ))
If it's for layout use only, you can specify with the Inspector.
I actually need to use it for a report: current month, month plus 1, month plus 2.
I have two tables of data (open purchase orders and open customer orders) which I need to total by item code for current month, next month and two months from current month.
I was hoping for it to populated automatically from the Get(currentdate) field, but when I do the calc as Month(Get(CurrentDate)) it's not displaying properly...maybe it's a layout thing like you suggested.
I will check it again tomorrow (east coast time) thanks for your help!
When dealing with dates I find this sort of thing very, very handy.
year(date) * 10^4 + month(Date(* 10^2
and you can go days
year(date) * 10^4 + month(Date(* 10^2 + day(Date)
For your question you can query 201606+1 and 201606-1.
Might help, might not, but file away somewhere - date "objects" as numbers are very useful.
“My first time in Madagascar was awesome because lemurs are kind of funny; they throw fruit at the back of your head when you’re not looking and then point at one another when you turn around.” ― Kevin Hearne
That does not matter. A numeral can still be text, and you can still do maths with it. Even if the field also contains text FM will just use the numerals. For example:
The calc: Month ( Get ( CurrentDate ) ) & " ( " & MonthName ( Get ( CurrentDate ) ) & " )"
—the result of which right now is: "6 ( June )"
Can still be used in another calc, say: <the field with the above calc> + 1
—the result of which will be: 7
Sometimes it doesn't matter, but sometimes it does. If they set the calc to return a date result, for example. And of course sorting is different for text vs. numbers. So I'd call it a best practice to use the appropriate data type.
We need hellkay to elaborate on "not displaying properly."
do you have data for currentmonth + 1 and currentmonth + 2 of current year ?
Keep in mind such a calculation must be unstored. Otherwise, it will store the value it had when you closed the Manage Database dialog basically forever (unless you change it there) because Get ( CurrentDate ) doesn't require any local dependencies. (That is, there's nothing forcing it to reevaluate.)
thanks for all the input.
I want the field to re-evaluate each time the report is run: I guess that would only happen on a new record creation?
The purpose of this DB is to manipulate imported data for reporting purposes, so I am effectively deleting and importing on a daily basis.
I was hoping to use this current month field to structure the relationships for the various reports mentioned above.
If that's all you're using it for, a set of global fields, populated by script, might work better.
Someone told me you couldn't use global fields in a hosted file, but I'm not sure it this is true...
I'm just a beginner at scripting: would you set a variable as being CurrentDate then go from there?
Not if you need it in a relationship.
Yes, you can certainly use global fields in a hosted system. They just revert to the value they had when last the file was closed locally (i.e. not hosted). In this case, it won't matter because you're setting them via script anyway.
hellkay wrote: Someone told me you couldn't use global fields in a hosted file, but I'm not sure it this is true...
Global fields are the key to offering a unique experience to different users accessing the same data pool.
Whoever told you that has no clue.
Let's back up for a sec. You said your goal is to get monthly totals. Why not start simple:
1. Create a number field. Let's call it OrderMonth.
2. Set OrderMonth to auto-enter Month(order date) -- or whatever date you care about.
3. Create a report that sub-summarizes by OrderMonth
Now it's all about the found set of records. Just do a Find for the three months you're interested in, Sort by OrderMonth, and you're done. If you use the report wizard (that comes up when you create a new layout) it does almost everything for you. You'll only need to adjust the Find script step.
But if it’s a number field,
Thanks - I've had fun testing all the suggestions in this thread, and this is the simple best answer for my needs right here (all others are filed away for future use!).
I just need to figure out how to trim the date name from the current date field, so that it returns just the number, not name.
Month ( DateField )
I love that feeling of fruit exploding in the back of your head.
Retrieving data ...