GWBush

Q: Dynamic Pop-up Menus

Part 1: Is it possible to have a pop-up menu reference a group of cells on a different table? I want the pop-up menu to get populated with the values in the other cells. Let me give you an example:

 

A1 = Banana

A2 = Orange

A3 = Apple

 

I would like to have a pop-up menu (on a different sheet) read:

     Banana

     Orange

     Apple

 

Part 2: If Part 1 was possible, is it also possible to have the pop-up menu update itself automatically? Let's say I add A4 = Pineapple to my table. Could I get my pop-up menu to automatically have "Pineapple" as an option or must I manually type it in as a selection?

 

Thank you so much for your help. I have tried to find this answer but have had no luck. Have a good day.

MacBook Air, OS X Mavericks (10.9.4)

Posted on Aug 12, 2014 7:51 PM

Close

Q: Dynamic Pop-up Menus

  • All replies
  • Helpful answers

Previous Page 2 of 3 last Next
  • by SGIII,

    SGIII SGIII Aug 16, 2014 8:18 AM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 16, 2014 8:18 AM in response to t quinn

    quinn,

     

    Making some progress on this but will have to come back to it later.

     

    SG

  • by SGIII,

    SGIII SGIII Aug 16, 2014 8:44 PM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 16, 2014 8:44 PM in response to t quinn

    quinn,

     

    Not exactly what you were asking for (yet), but maybe you and Ian could have a look at this proof of concept.

     

    1. Set up "submenu" tables as shown, and format them as Pop-Up Menu.
    2. Add a two-column data-entry table.
    3. Add values in column A of the data entry table that match the names of the "submenu" tables.
    4. Copy script into AppleScript Editor and run.

     

    The result should be Pop-up Menu cells in column B whose items depend on what was entered in column A.

     

    Screen Shot 2014-08-16 at 11.22.31 PM.png

    Screen Shot 2014-08-16 at 11.30.15 PM.png

     

     

    If it works on your end, then can refine to make it easier to apply to "real-world" situations. 

     

    (The Fruits table isn't used in this example and there are no "submenus" tables for Apple and Pineapple).

     

     

    SG

     

    property dataTable : "Table 1"
    property subMenuCol : 2
    tell application "Numbers"
      set s to front document's active sheet
      tell s to set t to table dataTable
      tell t
      repeat with i from 2 to count rows
      try
      tell s's table (row i's first cell's value)
      set selection range to last cell
      activate application "Numbers"
      tell application "System Events" to keystroke "c" using command down
      end tell
      set selection range to column subMenuCol's cell i
      tell application "System Events" to keystroke "v" using command down
      end try
      end repeat
      end tell
    end tell
    
    
    
  • by t quinn,

    t quinn t quinn Aug 17, 2014 10:26 AM in response to SGIII
    Level 5 (4,995 points)
    Mac OS X
    Aug 17, 2014 10:26 AM in response to SGIII

    SG,

     

    This is working well for me. I changed "Table 1" to "Checklist" here

    property dataTable : "Checklist"

    Screen Shot 2014-08-17 at 9.17.17 AM.png

    I don't quite understand how this script works but here is my understanding of what it does:

    Looks for value in dataTable Column A that matches a table name on the same sheet

    Copies the value of the last entry in named table into column B where the match is found.

     

    Since the script only takes the last entry in the table I tried using "Microphone" as a single cell table. While I don't see it as best practices, I would be tempted to just edit the popup there. And it propagates to wherever Microphone is in column A. As a note, the mixer table's last cell is a popup with the blank chosen.

     

    If  the "2" in property subMenuCol : 2  is changed to "1", then "Microphone" would be overwritten with the popup from the Microphone table. This is great. I can call the popup to any column A cell if I enter the table name of the popup.

    How would I adjust this script if I wanted it to look in column C for the popup table name?


    This seems to be getting really close in answering GW's (and so many others') question about dynamic popup menus. It does, in fact, do what he has asked except the table needs to be on the same sheet and once you have made a selection your popup won't update.


    quinn

  • by t quinn,

    t quinn t quinn Aug 17, 2014 2:31 PM in response to t quinn
    Level 5 (4,995 points)
    Mac OS X
    Aug 17, 2014 2:31 PM in response to t quinn

    If  the "2" in property subMenuCol : 2  is changed to "1", then "Microphone" would be overwritten with the popup from the Microphone table. This is great. I can call the popup to any column A cell if I enter the table name of the popup.


    This doesn't seem to work if the value "Microphone" is in a popup, only if it is text.  Rats.


    quinn

  • by SGIII,

    SGIII SGIII Aug 17, 2014 7:07 PM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 17, 2014 7:07 PM in response to t quinn

    Hi quinn,


    Your description of what the script does is right on.

     

    Here is a more generalized form addressing your questions.  The property names are more descriptive.  If you want to look in column C for the name of the table with the menu values, you would change subMenuCol to 3.

     

    If you want to keep all your tables with menu values on a separate sheet you would change nameOfSheetWithMenuValuesTables. (That works fine, but causes a "jumping to and back" animation.)

     

    The script is set up to copy the last cell in a table with the menu values, which in my example is always formatted as Pop-Up Menu set to 'None' (blank).  But if you want the default value in the 'submenu' to be something other than blank you could have the script point to another cell in the table (change 'to last cell' to 'to second cell' or 'to cell 2', etc., etc.).

     

    This script could easily be turned into an "app" that runs entirely automatically in the background at specified intervals if one typically enters new data in fairly large batches.

     

    SG

     

    property nameOfDataEntrySheet : "Sheet 1"
    property nameOfSheetWithMenuValuesTables : "Sheet 1"
    property nameOfDataEntryTable : "Table 1"
    property mainMenuCol : 1 --> specify the column with the "bigger" categories 
    property subMenuCol : 2 --> specify  the column for the "submenu" pop-ups "dependent" on the bigger categories
    tell application "Numbers"
      set d to front document
      set s to d's sheet nameOfDataEntrySheet
      set m to d's sheet nameOfSheetWithMenuValuesTables
      set t to s's table nameOfDataEntryTable
      tell t
      try
      repeat with i from 2 to count rows
      tell m's table (row i's cell mainMenuCol's value)
      set selection range to last cell
      activate application "Numbers"
      tell application "System Events" to keystroke "c" using command down
      end tell
      set selection range to column subMenuCol's cell i
      tell application "System Events" to keystroke "v" using command down
      end repeat
      end try
      end tell
    end tell
    
    
    
  • by t quinn,

    t quinn t quinn Aug 17, 2014 10:00 PM in response to SGIII
    Level 5 (4,995 points)
    Mac OS X
    Aug 17, 2014 10:00 PM in response to SGIII

    Hi SG,

     

    This is very neat stuff. I am starting to understand how you are putting these things together.

     

    I am experiencing a glitch in that I have to run the script multiple times for it to completely do its job. Here is a screenshot after the first run of the script:

    Screen Shot 2014-08-17 at 10.53.28 PM.png

    If I run it 4 times I will get all the popups in column C to propagate. Running Activity Monitor doesn't show anything getting overwhelmed. I am at a loss as to why this is (or isn't) happening. Any Ideas?

     

    quinn

  • by SGIII,

    SGIII SGIII Aug 18, 2014 5:21 AM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 18, 2014 5:21 AM in response to t quinn

    Probably there's an error that causes the script to fail silently.  Still pretty experimental. It doesn't (yet) have proper error checking.

     

    Try taking the 'try' and 'end try' lines out to see where the error(s) occur(s).

     

    The try block in AppleScript is a close equivalent of IFERROR.

     

    SG

  • by t quinn,

    t quinn t quinn Aug 18, 2014 10:21 AM in response to SGIII
    Level 5 (4,995 points)
    Mac OS X
    Aug 18, 2014 10:21 AM in response to SGIII

    Hi SG,

     

    I am not so sure there is an error. I removed the try/end try and script ran fine- except for the missed pastes. Here is the Event from one of those missing cells:

    Screen Shot 2014-08-18 at 11.17.23 AM.png

    I am seeing what you mean about this forum software and scripts. This is a screenshot. Seems to be showing it is doing what it is supposed to.


    On anoather note, The script works substantially better if I convert my control column to text first.


    quinn

     



  • by SGIII,

    SGIII SGIII Aug 18, 2014 10:27 AM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 18, 2014 10:27 AM in response to t quinn

    Hi quinn,

     

    Tough to see what's going on.  Are those "blank" cells in column C after the first run actually Pop-Up Menu cells showing the default 'None'?  Or are they true blank cells?

     

    And why would you want to have the same value in column C as you have in column B?  Wouldn't you want "submenu" choices there.

     

    To post scripts the closest I can get is 'use advanced editor' and choose >> syntax highlighting plain.

     

    SG

  • by t quinn,

    t quinn t quinn Aug 18, 2014 11:05 AM in response to SGIII
    Level 5 (4,995 points)
    Mac OS X
    Aug 18, 2014 11:05 AM in response to SGIII

    Hi SG,

     

    Yes, Those are blank cells.

     

    This is an expanded version of a solution I offered here:

    Problem with Formula - multiple outcome IF then statement all the popups are in one column, with a show column revealing the next choice.

    I have been holding out hope that I could have your script overwrite the lone popup column.

     

    quinn

  • by SGIII,

    SGIII SGIII Aug 18, 2014 11:23 AM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 18, 2014 11:23 AM in response to t quinn

    The script works substantially better if I convert my control column to text first.

     

    What do you mean by "control" column? If it's the "mainMenu" column and you've formatted it as Pop-Up Menu instead of text, then you can try using 'mainMenuCol's formatted value' instead of 'mainMenuCol's value'

     

    Why is it that you want the same value in C as you have in B?

     

    Do the tables with the values for the submenus have names that exactly match the values in 'mainMenuCol'?

     

    If you post what the menu tables look like I can troubleshoot.

     

    Because this is a gui script (no choice, unfortunately) sometimes you have to use the delay statement to allow the interface time to keep up.

     

    SG

  • by t quinn,

    t quinn t quinn Aug 18, 2014 2:55 PM in response to SGIII
    Level 5 (4,995 points)
    Mac OS X
    Aug 18, 2014 2:55 PM in response to SGIII

    Hi  SG,

     

    Easier to just show you. Here is my solution for a 3 deep conditional popup.

    Toni popup.numbers

    The one I have been playing with is only 2 deep. I have broken its functionality at the moment or I woud load it up.

     

    What I would like is to be able to overwrite the B column with refreashed popups. this may not be trustworthy on my machine. Here are the popup tables from the doc I have been playing with. You can see sheet 1 here: in response to t quinn.

    Screen Shot 2014-08-18 at 3.45.46 PM.png

     

    Where would I use the delay statement in this script of yours? It might be all it takes for this case?

     

    quinn

  • by SGIII,

    SGIII SGIII Aug 18, 2014 8:58 PM in response to t quinn
    Level 6 (10,627 points)
    Mac OS X
    Aug 18, 2014 8:58 PM in response to t quinn

    Where would I use the delay statement in this script of yours? It might be all it takes for this case?

     

     

    Try it here:

    Screen Shot 2014-08-18 at 11.36.36 PM.png

     

    Increase the value if it still skips some rows. Decrease the value if you can get away with it on your machine. The script is a bit pokey but it does get the job done here:

     

    Screen Shot 2014-08-18 at 11.44.45 PM.png

     

    SG

  • by GWBush,

    GWBush GWBush Aug 21, 2014 11:52 PM in response to GWBush
    Level 1 (0 points)
    Aug 21, 2014 11:52 PM in response to GWBush

    Thank you for the replies. I wish I had the skills that you all had to manipulate pop-up menus. I think my only hope is to have Apple update Numbers so the process isn't so complex. Thanks for the help.

  • by t quinn,

    t quinn t quinn Aug 22, 2014 7:03 AM in response to GWBush
    Level 5 (4,995 points)
    Mac OS X
    Aug 22, 2014 7:03 AM in response to GWBush

    Hi GW,

     

    I actually know how you feel. I am trying to wrap my brain around Apple Scripting because that seems the only way to get our popups to update without doing it manually.

    In my situation, I want to be able to update the popups that haven't been used in a table. I have figured out how to flag them and am trying to figure out how to modify SG's script to only look for flagged rows.

     

    Keep an eye out for a User Tip from Yellowbox on popups. He may roll it out in stages and dynamic popups will be a later stage. They will probably involve Apple Scripts but once they are written it is relativly easy to use them.

     

    ciao,

     

    quinn

Previous Page 2 of 3 last Next