13 Replies Latest reply: Feb 7, 2014 11:25 AM by Jerrold Green1
bobbyv33 Level 1 Level 1 (0 points)

I have been keeping a schedule of projects for my business using numbers 2.9 for a few years. It worked pretty well. When 3.0 came out, It didn't work for me because of the single column sorting thing. But now that they have re-introduced multi-column sorting, I'd like to give the listing a try again in 3.1, but I need a little help..

 

The way my schedule works is this, there are 3 pages, Orders in, Open Orders, and Invoices (finished orders). I generally would enter an order on the "orders in" page, keeping  a list by day, copy them to the Open orders page (which becomes the schedule of things to do), and then, when the project is done, cut from the open orders page to the Invoices page, pretty simple, right?

 

Here is the rub, Each page, obviously, has a different use and therefor different columns. On the orders in page, we would enter orders, one per row, and have all the columns we needed in each of the three different pages, some even had formulas in them, but hid the ones that weren't needed on that page. In 2.9. when you copied a row and pasted it into a different table, it would paste only the columns that were visible in the original copy, just as 3.1 does now, but if you INSERTED the row, it would paste column A into column A and column B into column B regardless of it's visiblility (which, to me seems to be a no brainer as to how it SHOULD behave, but who am I to say). But now, in 3.1 inserting, now, behaves the same, pasting only the visible columns in, seemingly, random places.

 

Are there any ideas of how to do this more efficiently?

  • Wayne Contello Level 6 Level 6 (14,915 points)

    I would suggest having all orders in the same list and using filters to see the surrent ones.  Have a status column with a pop-up to identify "Orders In", "Active", and "Invoice"

     

    Then make a summary page(s) (if necessary) to see groups of orders in those states:

     

    Date

    Order Number

    Invoice Number

    Status

    Other informaiton

     

    O20140001

    I20140001

    Invoice

     

     

    O20140002

    I20140002

    Invoice

     

     

    O20140003

     

    Active

     

     

    O20140004

     

    Active

     

     

    O20140005

     

    Active

     

     

    O20140006

     

    Active

     

     

    O20140007

     

    Order In

     

     

    O20140008

     

    Order In

     

     

    O20140009

     

    Order In

     

     

     

    Filtered on Status:

    Date

    Order Number

    Invoice Number

    Status

    Other informaiton

     

    O20140003

     

    Active

     

     

    O20140004

     

    Active

     

     

    O20140005

     

    Active

     

     

    O20140006

     

    Active

     

     

     

    Filter is set up like this:

    Screen Shot 2014-01-28 at 8.41.05 AM.png

  • bobbyv33 Level 1 Level 1 (0 points)

    Different lists serve different purposes. I can't use the same list and print out different versions of it.

     

    Orders in needs to tell me daily totals, averages, who ordered what and when. Summaries as well. Same for invoices. Cerrtainly I can sort and hide and unsort and resort and filter, but that is way too complicated for me to have the lists that i need. In addition, the Orders list and the invoice list start from 0 every month while the open projects list is continually updated.

     

     

     

    Screen Shot 2014-01-28 at 10.23.54 AM.jpg

    This is a part of the enter order (invoices looks similar), notice that column B, C and E are hidden.

    Screen Shot 2014-01-28 at 10.23.43 AM.jpg

     

    This is a section of the open orders page. Note that now we see B & C but E, which is used in the invoice page (for the invoice date) is still hidden.

  • bobbyv33 Level 1 Level 1 (0 points)

    BTW, Wayne, Thanks for your suggestion. I think my problem with your suggestion is that I would end up with a never ending list that would just be too huge to handle, unless I am not understanding what you are saying.

  • Wayne Contello Level 6 Level 6 (14,915 points)

    Bobby,

     

    Then separate by years in different sheets.  The simpler the solution the eaiser it is to maintain.

  • bobbyv33 Level 1 Level 1 (0 points)

    I think that would be more complicated, unless I am missing something. Thanks to cut and paste doing it on three separate sheets is very simple.

     

    enter the order. Copy the entered order and PASTE to open orders. When the order is completed, CUT from open orders and pasted to completed orders. Couldn't be any simpler.

     

    No filtering on and off, no changing of the status (cutting and pasting is easier than changing the status, no?), no complicated formulas based on status for the sumaries, no huge pages.

     

    Am I missing something, Wayne?

  • Wayne Contello Level 6 Level 6 (14,915 points)

    I think this is a difference of opinion and me not really understanding your problem.  Try showing more context and exaplin your workflow again.

  • bobbyv33 Level 1 Level 1 (0 points)

    I keep three different lists. I keep them SEPARATELY from our bookkeeping system as a check. I run the production so I like to reconcile production with bookkeepping.

     

    List one, orders in. totaled by day. We need to keep averages and statistics daily. totaled and restarted monthly.

     

    List two. Open orders, sorted by due date. Used as a production schedule. Continuous, never restarted.

     

    List three, Finished orders. totaled by day. Once again, needed with daily averages and stats. Totaled and restarted monthly.

     

    Orders are entered, as they come in, on list one. Copied from list one and pasted to list two. When the factory is finished with the order it is CUT from list two and pasted onto list 3. Keeping list two current with ONLY orders to be worked on, List one with a monthly list of orders entered, totaled by day, and list three current, daily, with completed orders. The three lists are kept as sheets in one file.

     

    At the end of every month, I duplicate list three and rename it to the month (ie Jan 14). I then cut all the info from list one and paste it to the last month file for archiveing. I then delete the info in list one and start again (not doing anything to list two other than the normal daily changes).

     

    I came up with this system while studying aviation where the control tower moves tiles from one list to another representing planes in the air.

     

    Once again, thanks for your help.

  • SGIII Level 5 Level 5 (5,265 points)

    Hi bobbyv33,

     

    I can see the advantages of simply copying from one list to another.

     

    I've noticed that you can drag a row from a table to another table.  To drag a row click the row number and move the cursor with the mouse button held down (or trackpad equivalent) to "lift" the row out of the table and drop insert it where you want, in that table or another. (The maneuver is little tricky at first but comes with practice.)

     

    Screen Shot 2014-02-06 at 12.43.41 PM.png

     

    Even if the destination table has hidden columns, you get the result you want (with the values in the right cells, including the hidden column--you can unhide it to verify):

     

    Screen Shot 2014-02-06 at 12.46.55 PM.png

     

    But if you hold the option key down while doing this (to copy the row rather than move it) then on my maching the cells in the resulting row in the destination table do not line up correctly. I suspect this is a bug, unless someone else can figure it out.

     

    In any case you do have simple drag and drop (achieving the effect of cut and paste). So that may make Numbers 3.1 suitable for your workflow. The problem is where you need to add a row to another table but leave it in the original table as well.

     

    If you get the same results as I do, perhaps report this to Apple via Numbers > Provide Numbers Feedback in your menu?

     

    SG

  • Jerrold Green1 Level 7 Level 7 (29,855 points)

    Bobby,

     

    I've read the thread and I understand your points and the suggestions that have been offered. If I was doing this for myself, I would have one Master Table with a row (record) for each order or Job. The Master Table would have Date Received, Customer ID, Order Number, Job Description, Quoted Price, Date Production Started, Date Shipped, Date Payment Received(plus other important details). Nothing would ever leave that table unless you were tired of looking at it or if it became difficult to navigate because of its size. At that time I would achieve what I didn't want to be tripping over. (This achieve in addition to a robust daily backup routine on the active file.)

     

    Then I would create three or more distinct tables for listing jobs in queue, jobs in production and jobs shipped awaiting payment, etc. These sub-tables would be customized to their exact business needs for their particular phase of the project. They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

     

    This is all easy with Numbers, unless your needs are considerably more stringent than what you have described.

     

    Jerry

  • SGIII Level 5 Level 5 (5,265 points)

    They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

     

    This is all easy with Numbers, unless your needs are considerably more stringent that what you have described.

     

    Hi Jerry,

     

    Pulling the data out of a master table automatically and summarizing it is neat, using the techniques you and others have pioneered here. But if you need to be able to sort the sub-tables, and yet still have them pull data from the master table automatically, things seem to get more complicated.

     

    I suppose one way to handle this would be to have sub-tables set up that pull the data automatically. Then every time you need to sort a sub-table, command-c to copy the table (as an object, not just the contents) and Edit > Paste Formula Results to get a new version of the table that you can sort, keeping the old one intact to keep updating from the master table.

     

    Not too bad. But there is something to be said for being able to drag and drop a copy of a row where you want it, leaving the original in a master table for record keeping purposes. The control tower moving tiles from one list to another is a good system, though in this case it would be good to also keep a pile of the tiles in their original placement, which is not as convenient in Numbers 3 as it should be.

     

    SG

  • bobbyv33 Level 1 Level 1 (0 points)

    Dragging the rows works perfectly, thanks. It hides and unhides the colums exactly as I wanted thanks.

     

    A couple of questions though....

     

    do you know of a way to drag the row from one sheet to another? What I ended up doing was copying the whole table to the new sheet and then taking the rows, one at a time into the new table. I will try doing that a while unless we come up with a better solution.

     

    There also seems to be a problem draging the last row (a single row in a table). Dragging with the option key does duplicate the row, but when it is inserted the hidden columns don't adjust so the come out the same as just inserting (that must be a bug, I would think that duplicating and dragging and dragging would behave the same way, I will report it). Adding. a blank line to the bottom of the copied table seems to solve that problem.

     

    My work around was to copy the table from one sheet to the next  (or create a table from the rows I needed). and then adjust, manually, the hidden columns. then copy and paste them where they need to be.
    d for a few days and see.

     

    I am not sure which is easier. I will try the new metho

  • SGIII Level 5 Level 5 (5,265 points)

    If there is a way to drag a row from a table in one sheet to a table in another sheet, I haven't found it. That is probably a design limitation that will be hard to change.

     

    But the inability to cut and paste a row, or copy and paste a row, while respecting hidden columns in the destination table on another sheet, seems to be a bug.

     

    As does the fact that you can't option drag (i.e. copy) a row from one table to another table on the same sheet and have it respect any hidden columns. As you say, you would think one could do that since it is possible to just drag and drop (i.e. move) with good results even when there are hidden columns.

     

    SG

  • Jerrold Green1 Level 7 Level 7 (29,855 points)

    SGIII wrote:

     

    They would pull data automatically from the Master Table based on dates and other status fields in the Master Table.

     

    This is all easy with Numbers, unless your needs are considerably more stringent that what you have described.

     

    Hi Jerry,

     

    Pulling the data out of a master table automatically and summarizing it is neat, using the techniques you and others have pioneered here. But if you need to be able to sort the sub-tables, and yet still have them pull data from the master table automatically, things seem to get more complicated.

     

    I suppose one way to handle this would be to have sub-tables set up that pull the data automatically. Then every time you need to sort a sub-table, command-c to copy the table (as an object, not just the contents) and Edit > Paste Formula Results to get a new version of the table that you can sort, keeping the old one intact to keep updating from the master table.

     

    Not too bad. But there is something to be said for being able to drag and drop a copy of a row where you want it, leaving the original in a master table for record keeping purposes. The control tower moving tiles from one list to another is a good system, though in this case it would be good to also keep a pile of the tiles in their original placement, which is not as convenient in Numbers 3 as it should be.

     

    SG

    SG,

     

    Please excuse my tardy reply. I guess I'm not the best one to ask about dragging and dropping Rows in Numbers 3. I've comfirmed your observations, but beyond that I don't have much experience with it.

     

    In the context of this thread, I'm not a fan. To me that's too much manual activity, subject to error, whereas once automatic fetching is debugged it should be very reliable. Yet, it seems the OP is pretty set on that approach, so if I can come up with something better along those lines, I'll post back.

     

    Jerry