Skip navigation

Insert a fixed date by use of checkbox.

1017 Views 8 Replies Latest reply: Mar 22, 2013 12:26 PM by Sharbonous RSS
DTM82 Level 1 Level 1 (0 points)
Currently Being Moderated
Jun 20, 2011 3:50 AM

I have tried everything I can think of to solve this problem, I don't know if it can be done.

 

I  have a table of checkboxes which monitor the completion of tasks.  I have another table (identical in layout to the first) which I want to store the date of completion of those tasks.  I.e. when the checkbox is ticked (becomes 'TRUE') the the corresponding cell in table 2 will update with the date.  I do not want the cells to continue updating after that time.

 

I have tried all of the possible date functions I am sure.  The problem is that the only way I can get the date there is by some use of the NOW() function.  The NOW() function continuously updates.

 

Is there a way of forcing the cell to only update when the checkbox is clicked?

 

 

Here are some of the formulae I have used:

=IF(A5=TRUE,NOW(),"TBC")

=IF(A5=TRUE,DATEVALUE(NOW()),"TBC")

=IF(A5=TRUE,DATEVALUE(TODAY()),"TBC")

=IF(A5=TRUE,DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),"TBC")

 

 

Your help will be greatly appreciated (even if it is to tell me I cannot do this!).

MacBook 1st G, MacBook Pro (Al), iMac, Mac OS X (10.6.7)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Jun 20, 2011 4:55 AM (in response to DTM82)

    Question asked and answered several times.

     

    The function NOW is triggered by every change made somewhere in the document.

    From my point of view, the best answer to this kind of need is given by the free WordServices from Devon Technologies which offer the ability to insert a "dead" date value thru a shortcut.

    I don’t use it in my docs because it doesn't behave correctly with the date format used in France.

    So I built my own service thanks to the free app : ThisService.app

    available from http://wafflesoftware.net/thisservice/

     

    An other scheme rely upon a modified version of a script which I posted here several times.

    Stay tuned.

     

    Yvan KOENIG (VALLAURIS, France) lundi 20 juin 2011 13:45:33

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Jun 20, 2011 5:36 AM (in response to KOENIG Yvan)

    Here is the script :

     

    --{code}

    --[SCRIPT store_check_and_date]

    (*

    Enregistrer le script en tant que Script  : store_check_and_date.scpt

    déplacer le fichier créé dans le dossier

    <VolumeDeDémarrage>:Utilisateurs:<votreCompte>:Library:Scripts:Applications:Numb ers:

    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

     

    Placez le curseur dans la cellule dont la case à cocher doit être définie.

    Aller au menu Scripts , choisir Numbers puis choisir  “store_check_and_date”

     

    La case à cocher est ajustée suivant le choix effectué.

    La cellule de coordonnées ligne_cellule + decalage_vertical , colonne_cellule + decalage_horizontal reçoit la date_heure de l'intervention.

     

    L'aide du Finder explique:

    L'Utilitaire AppleScript permet d'activer le Menu des scripts :

    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

    Cochez la case "Afficher le menu des scripts dans la barre de menus".

     

    +++++++++

     

    Save the script as a Script : store_check_and_date.scpt

     

    Move the newly created application into the folder:

    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

     

    Put the cursor in the cell whose checkbox must be defined.

     

    Go to the Scripts Menu, choose Numbers, then choose “store_check_and_date”

     

    The checkbox of the pointed cell is set according to your choice

    The cell at row_cell + decalage_vertical , column_cell + decalage_horizontal receive the current date_time.

     

    The Finder's Help explains:

    To make the Script menu appear:

    Open the AppleScript utility located in Applications/AppleScript.

    Select the "Show Script Menu in menu bar" checkbox.

     

    Yvan KOENIG (VALLAURIS, France)

    2011/06/20

    *)

    --=====

     

    property decalage_horizontal : 1

    property decalage_vertical : 0

    property theApp : "Numbers"

     

    --=====

     

    on run

              local TRUE_loc, FALSE_loc, dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2, maybe, cdt

              set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

     

              set TRUE_loc to my getLocalizedTabularString("Numbers", "TRUE")

              set FALSE_loc to my getLocalizedTabularString("Numbers", "FALSE")

     

              tell application (path to frontmost application as string)

                        if my parleAnglais() then

                                  set maybe to (choose from list {TRUE_loc, FALSE_loc} with prompt "Select the status of the checkbox")

                        else

                                  set maybe to (choose from list {TRUE_loc, FALSE_loc} with prompt "Select the status of the checkbox")

                        end if

              end tell

              if maybe is false then error number -128

              set maybe to (item 1 of maybe) as text

     

              set cdt to my cleanThisDate(current date) (* the new date_time as a clean date_time *)

              tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

                        tell cell rowNum1 of column colNum1

                                  set format to text

                                  set value to (maybe is TRUE_loc)

                                  set format to checkbox

                        end tell

                        set value of cell (rowNum1 + decalage_vertical) of column (colNum1 + decalage_horizontal) to (cdt as text)

              end tell -- application …

     

    end run

     

    --=====

     

    on cleanThisDate(dt)

      (* ugly code but once I got date_time with milliseconds so if necessary, I drop them *)

              local l

              set l to my decoupe(dt as text, ":")

              if (count of l) = 4 then

                        set dt to date (my recolle(items 1 thru 3 of l, ":"))

              end if

              return dt

    end cleanThisDate

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    *)

    on get_SelParams()

              local d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2

              tell application "Numbers" to tell document 1

                        set d_name to its name

                        set s_name to ""

                        repeat with i from 1 to the count of sheets

                                  tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

                                  if maybe is not 0 then

                                            set s_name to name of sheet i

                                            exit repeat

                                  end if -- maybe is not 0

                        end repeat

                        if s_name is "" then

                                  if my parleAnglais() then

                                            error "No sheet has a selected table embedding at least one selected cell !"

                                  else

                                            error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

                                  end if

                        end if

                        tell sheet s_name to tell (first table where selection range is not missing value)

                                  tell selection range

                                            set {top_left, bottom_right} to {name of first cell, name of last cell}

                                  end tell

                                  set t_name to its name

                                  tell cell top_left to set {row_num1, col_num1} to {address of its row, address of its column}

                                  if top_left is bottom_right then

                                            set {row_num2, col_num2} to {row_num1, col_num1}

                                  else

                                            tell cell bottom_right to set {row_num2, col_num2} to {address of its row, address of its column}

                                  end if

                        end tell -- sheet…

                        return {d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2}

              end tell -- Numbers

    end get_SelParams

     

    --=====

     

    on parleAnglais()

              local z

              try

                        tell application theApp to set z to localized string "Cancel"

              on error

                        set z to "Cancel"

              end try

              return (z is not "Annuler")

    end parleAnglais

     

    --=====

     

    on decoupe(t, d)

              local oTIDs, l

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d

              set l to text items of t

              set AppleScript's text item delimiters to oTIDs

              return l

    end decoupe

     

    --=====

     

    on recolle(l, d)

              local oTIDs, t

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d

              set t to l as text

              set AppleScript's text item delimiters to oTIDs

              return t

    end recolle

    --=====

    (*

    Useful to get function’s localized name if we need to build formulas

    examples:

    set OFFSET_loc to my getLocalizedTabularString("Numbers", "OFFSET")

    set ADDRESS_loc to my getLocalizedTabularString(theApp, "ADDRESS")

    set INDIRECT_loc to my getLocalizedTabularString(theApp, "INDIRECT")

     

    Requires :

    decoupe()

    get_iWorkNum()

    getLocalizedName()

    *)

    on getLocalizedTabularString(theApp, x)

              return my getLocalizedName(theApp, x, (path to application support as text) & "iWork '" & my get_iWorkNum(theApp) & ":Frameworks:SFTabular.framework:Versions:A:Resources:")

    end getLocalizedTabularString

     

    --=====

     

    on get_iWorkNum(a)

              local verNum

              tell application a to set verNum to item 1 of my decoupe(get version, ".")

              if (a is "Numbers" and verNum is "2") or (a is "Pages" and verNum is "4") then

                        return "09"

              else

                        return "11"

              end if

    end get_iWorkNum

     

    --=====

     

    on getLocalizedName(a, x, f)

              tell application a to return localized string x from table "Localizable" in bundle file f

    end getLocalizedName

     

    --=====

    --[/SCRIPT]

    --{code}

     

    Of course as I never received an answer about the exact location of the app and the Frameworks files when Numbers is bought on mac App Store, the script is designed for Numbers as it is installed from the iWork package.

    At this time, I know that when it installs the app, mac App Store installs a package named Numbers.app embedding the FrameWorks folders.

    I don't know if the FrameWorks remain located this way or if they are moved in the folder :

    Macintosh HD:Library:Application Support:iWork '09:


    Yvan KOENIG (VALLAURIS, France) lundi 20 juin 2011 14:36:13

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Jun 20, 2011 6:11 AM (in response to DTM82)

    At this time, Numbers doesn't offer the ability to frigger an external item.

    This is why I took the opposite road : trigger Numbers from an external item.

     

    Yvan KOENIG (VALLAURIS, France) lundi 20 juin 2011 15:10:57

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • Sharbonous Calculating status...
    Currently Being Moderated
    Mar 22, 2013 8:49 AM (in response to KOENIG Yvan)

    Hi,

     

    Please can you show me how to tell AppleScript to move a check from Username to Pin Code checkbox?

    If you can I really would appreciate it. Thank you very much in advance.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 22, 2013 10:42 AM (in response to Sharbonous)

    Hi Sharbonous,

     

    This is a totally separate question from the one identified by the subject line in this discussion. Please repost your question as a new topic, with a subject line that would be useful to someone searching for an answer to a similar issue late.

     

    Regards,

    Barry

  • Sharbonous Level 1 Level 1 (0 points)
    Currently Being Moderated
    Mar 22, 2013 12:26 PM (in response to Barry)

    Thank you, will do.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.