1 2 Previous Next 24 Replies Latest reply on Oct 24, 2012 4:50 AM by willrollo

    Date cond format

      Title

      Date cond format

      Post

           This is a continuation of a previous post that was solved but has returned to haunt me! 

            

            
            
           I have come back to this original issue as it appears that this calc/cond format has a few issues since I redid the my db.
            
           I cannot see why though as the fields seem to contain the same properties and data, but the result is not the correct one.
            
           using this conditional format 
            
            
            
           Let ( [ d = Invoiceitems_INVOICE::Week of month ;
            
                      
            
                      $$number = If ( d < 5 ; Choose ( d ; "" ; "We anticipate your fender being ready by the first week of " ; "We anticipate your fender being ready by the second week of " ; "We anticipate your fender being ready by the third week of " ) ;  "We anticipate your fender being ready by the last week of " ) & Invoiceitems_INVOICE::Month & ", 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."
            
                     ] ;
            
                      True )
            
            
            
           The resulting data in the field just reads " December (for example), and will advise you nearer the time. .
            
            
            
           The field, Invoiceitems::INVOICE::Week of month does calculate the correct result to trigger this - ie. a number from 0-5 depending on the day of the month.
            
           Any idea why this is happening?  The layout is based on the Invoiceitems::INVOICE table occurance.
            
           Thank you

        • 1. Re: Date cond format
          philmodjunk

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

          • 2. Re: Date cond format

                 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?

            • 3. Re: Date cond format

                   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!

                    

              • 4. Re: Date cond format

                     Thought I'd fixed it but no...

                     Prob above still occurs..

                      

                • 5. Re: Date cond format
                  philmodjunk

                       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?

                  • 6. Re: Date cond format
                    LaRetta_1

                         Hi Will,

                         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 ;
                         $$number =
                         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
                          ] ;
                         $$number
                          ) // 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.  :^)

                    • 7. Re: Date cond format
                      LaRetta_1

                           I cannot edit this website from iPad ... Of course change the calc portion back to True instead of $$number...

                      • 8. Re: Date cond format

                             Hi Phil and LaRetta,

                             Firstly, Phil:

                             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

                              

                        • 9. Re: Date cond format
                          philmodjunk

                               Why would the date for November 28 read as the first week of the yearMonth?

                               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.

                          • 10. Re: Date cond format

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

                                  

                            • 11. Re: Date cond format

                                   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
                              • 12. Re: Date cond format
                                philmodjunk

                                     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.

                                • 13. Re: Date cond format

                                       Sorry Phil - I never saw that..I tend to rely on the email alerts!

                                  • 14. Re: Date cond format
                                    philmodjunk

                                         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.

                                    1 2 Previous Next