I am guessing that you put $$Number on your layout as a merge variable?
What object on your layout has this conditional format expression?
Have you tried refreshing the window after changing the value of Week of Month? (Requires a script step.)
Hi Phil - thank you.
Yes, I have $$number as a mergefield. It is in its own text box that has the cond. format appied.
I have tried the refresh window script step but nothing happens...
Any other ideas/info required to help?
I have managed to get it working to a certain point (not sure how! must have been a glitch somewhere)
Anyway, my numbering seems to be 'off ' by one.
Eg for the 6th November, the result reads second week of november.
the calculationI have to create the number of thwe week within a month is below:
WeekOfYear(Due_Date) - WeekOfYear (Due_Date - Day ( Due_Date ) + 1) + 1
It gives a result of 2, to the 6th of a month.. I know I could just remove a 1, but this did used to work!
Thought I'd fixed it but no...
Prob above still occurs..
sounds like Invoiceitems_INVOICE::Week of month might not be accessing a value and thus d evaluates as zero.
Is your layout based on invoiceitems_Invoice? (Is it selected in "show records from" in Layout Setup?)
If not, is Invoiceitems_Invoice a related occurrence of the occurrence that is selected in "show records from"?
If you place this field directly on this same layout, do you see a value in it?
Your calculation is not evaluating properly if the WeekOfMonth is 0 or 4. Choose() begins with 0. You can see for yourself if you create a calculaton of this and change the True to $$number. Look at just this portion of your calc again and notice my red notes:
If ( d < 5 ; Choose ( d ;
"" ; this is 0 which will produce "0, and will advise you nearer the time"...
"We anticipate your fender being ready by the first week of " ; this is 1
"We anticipate your fender being ready by the second week of " ; this is 2
"We anticipate your fender being ready by the third week of " this is 3
) ; // END CHOOSE <-- notice end parenthesis for the Choosemeaning 4 is skipped and also producing "4, and will advise you nearer the time"...
"We anticipate your fender being ready by the last week of "
) // END IF
& WeekOfMonth & ", and will advise you nearer the time.
Your calc can be corrected and written a bit more efficiently (and I think easier to read) as:
Let ( [
d = WeekOfMonth ;
If ( d and d < 5 ; // omits 0 from being evaluated at all
"We anticipate your fender being ready by the " & Choose ( d - 1 ; "first" ; "second" ; "third" ; "last" )
& " week of " & WeekOfMonth &
", and will advise you nearer the time. Please do not hesitate to telephone or email us if you would like to know how your fender is progressing. Our order reference is given above."
) // END IF
) // END LET
It appear that 0 and 5 should be completed ignored in this calculation but if I have misinterpreted your needs please let me know. :^)
I cannot edit this website from iPad ... Of course change the calc portion back to True instead of $$number...
Hi Phil and LaRetta,
The layout is based on invoiceItems_INVOICES and the merged field within it also is. It does show data when on the layout - just the wrong number..
which leads me into LaRetta's answer - thank you, your calc is far more concise than mine! However, it is giving the wrong result and I think this is down ot the calculation of the dateweekofmonth field as mentioned to Phil.
It gives this result (2) based on this calculation WeekOfYear(Due_Date) - WeekOfYear (Due_Date - Day ( Due_Date ) + 1) + 1
It should read 1
If the date is the 28/11/12 the field is 5
Due_Date is a date field that this whole thing is based on. it reads 04/11/12 at the moment so my merged field should say first week of november - it actually says second week of november
Why would the date for November 28 read as the first week of the
By my analysis, 5 is the correct week number returned as it falls on the last week of the month, which will be the 5th week.
The calc field I outlined above, I thought, gives the result of what number the week is in a certain month, not year.
If I make the due_date field 24 october, the result above would be 4, which should make the merge field read the fourth week of October, not the last week as it isnt..? Or am I getting confused>>? (wouldnt be the first time!)
I thought I might explain fully what I am trying to achieve, in case there is a better way of doing this..
I have three fields, Invoicedetails::Date_Due, invoicedetails::Date Approx and invoicedetails::DateOrderAck
Date approx either has the data "approx" or "". (triggered by another script)
If it is "approx", then the result in the invoicedetails::DateOrderAck should read (unless I do the merge field with $$number mentioned earlier)If the date_due field is 1 october"We anticipate your product being ready by the first week of october and will advise you nearer the time. Please do not hesitate to telephone or email us if you would like to know how your product is progressing. Our order reference is given above."If the date approx field is "" then I want the invoicedetails::DateOrderAck to read"Your fender will be completed by the agreed date, the 1st October. Please do not hesitate to telephone or email us if you would like to know how your fender is progressing. Our order reference is given above.Hope this clears things up a little to help provide a solution..Thank you both for your time
Please read my last post again. "Year" was a typo that I corrected immediately after posting, but such corrections do not appear in any emails sent out.
Sorry Phil - I never saw that..I tend to rely on the email alerts!
Which doesn't answer my question. Why would you expect the calc to return a 1 for November 28, 2012?
When I check a calendar, November 1-3 is week 1, 4-10 is week 2, 11-17 is week 3, 18-24 is week 4 and 25-30 is week 5. Thus a result of 5 for this date would seem to be the correct result.