Copy row from table to a view of the table

Hi!
I have a master list of stock symbols and the first column is for date and time, the second column are stock symbols and the third column is for notes. When I want to write a note about a symbol, I use a keyboard macro to put the current date and time in the first column, then write my note in the third column.

Separately, I have a view of the master table that is an archive of all these notes. Presently, I just cut and paste the new notes (columns 1, 2, 3) from the master list to the archive sheet. But I would love to be able to automate this…so everytime I add a date/time in the first column AND/OR a note in the third column it gets added to the archive view (in a perfect world, this would happen when a button was pushed or some other trigger by me).

Is this possible?

I tried to write an automation to do this…but sort of made a mess.

I also tried to do this with a Cross-Doc…but I don’t see my master table in the list to the right.

Any help you can offer will be greatly appreciated!

HI Gregg, welcome back.

When you say “I have a view of the master table that is an archive of all these notes.” Do you mean a copy in a different table, or a view of the existing table? Table entries always exist in any view of the table, unless they are filtered out.

I would like to suggest that you only have one table. This table will contain all the notes you have made. Then you create another view(s) of that table, where you put in filters, probably an interactive filter, so that you can select the time period and the ticker symbol you would like to see.

Regards
Piet Strydom
Fewer tables, more views

Hi, Piet…thank you for your reply!
It is one master table, and the archive is a view of that master table.
But I actually think that what I learned from you (and you may not like this!) is that I do need two tables…and here’s why:
After I cut and paste the the new notes (Col1: date and time, Col2: symbol, Col3: Notes) to the archive, I then erase the dates and times from the master table so that I can add new dates/times and notes on the next review.
Maybe that’s a bad idea all around…but it’s been my method to date.
I am actually going to try making the archive a new table…in fact, I have to confess I got confused about the difference between different tables and different views until a few minutes ago.
Thank you again for your reply…and if you think there’s a better way for me to accomplish my goal, I would love to hear about it!
Gregg

Just to update my own comments here…after creating a new archive table and messing around with Cross-Doc and Automations I realized that because I want to delete the dates/times and notes from the master list after they are added to the archive table, then the dates/times and notes get removed from the new archive table once the Cross-Doc syncs again or the automation runs again.

Is there a way to automate or a formula for cutting and pasting info from one cell to another in the same row?

Again, many thanks!

Gregg

And I now realize this is an impossible mission. :grinning:

hi @Gregg_Stebben , you might be able to inject info in the same column a bit higher or lower following these two suggestions:

  1. apply a ModifyRows via a button
  2. use tricks to acces previousRows or followingRows. The logic behind I wrote here.

I hope it helps.
Cheers, Christiaan

1 Like

Hi there @Gregg_Stebben :

Not sure if I understood correctly your setup but would this help ? :innocent:

(If yes, I can add informations later :wink: )

Edit : Or this maybe :innocent: …

If you had a single table, with an “archive view”, why were you cutting and pasting?

As far as using two tables so that you can add new entries, not sure why you need to delete the old entries first?

2 Likes

This is great stuff…I am going to have to brew a big pot of coffee and tackle this later tonight…it looks like a fun all-nighter!!

Thank you!
Gregg

This is very interesting…you’ve shown me a lot of tools here I didn’t know were possible…I will try these and see what happens!
Thank you!
Gregg

Hi, Piet…thank you for replying again!
I am thinking about your questions…they are helping me look at what I am trying to do with a fresh perspective.
You’ve given me some ideas here, and so have the others…I think I have a fun late night ahead of me trying some different things.
Thank you again!
Gregg

1 Like

Oh man, this is beautiful!
Both of these are better than I even imagined!!
Thank you so much!!!
Greg

1 Like

You’re welcome :blush: !

Glad to know you could make it work :blush: !
(without the explanations I should have add but didn’t got the time to :innocent: )

1 Like

I haven’t made it work yet…but I am figuring the formulas out.
This has been a great learning process for me…you showed me a lot of things with Coda I never knew were possible!
Thank you again!!
Gregg

Hi, Again!
I pretty much figured this out thanks to you and all your help!!
But I have one question:
How can I make the button trigger when there is an entry in the Date & Time column instead of the Notes column?
I tried figuring this out on my own, but I couldn’t make it work.
I love this solution, I am so excited about it.
Thank you again!
Gregg

Hi @Gregg_Stebben : Sorry for the delay but what do you mean by “trigger” :blush: ?

Ahhh!
In other words, the formula you put in the button doesn’t recognize that there’s data to archive until there’s an entry in the Notes field.
A better use case would be that an entry in the Date & Time field causes the button to recognize that there’s data to archive.
For Background: In practice, before every note is written, Date & Time are input. But sometimes I input Date & Time but no note (to remind myself to add a note later) and those entries with only Date & Time should be archived too.
Does that make sense?
I feel like I should be able to tweak your button formula to make this change…but I just can’t figure it out.
If you can help, I would be grateful.
Thank you!
Gregg

Ah :blush: ! It’s the Disable if formula in the button :wink: .

I’ve prepared another sample as you’ve added some useful context here (compared to what you said in your other posts, at that time, it seemed like the notes where the important part so I’ve used this as the main thing the button should look at, but if in fact the important part are the dates and times, that’s will work too :wink: ) but I’m going to begin with the Disable if part :blush: .

The formula I used in the button (see sample below :wink: ) is this one :

[Notes 3].Filter([Date & Time]="").Count()=[Notes 3].Count()

And what it does is :

  • Count how many rows in the table [Notes 3] in the field Date & Time are Blank
    :point_right: [Notes 3].Filter([Date & Time]="").Count()
  • Count how many rows there are in the table [Notes 3]
    :point_right: [Notes 3].Count()

And then, if the Count() for the Blank rows in Date & Time is equal to the total number of rows in the table [Notes 3] , the button gets disable :blush: (because it means that all the rows (at least in the Date & Time field) in the table are empty. If it’s not the case and they are different (!= ), it means there’s at the very least one row with a Date & Time :blush: .

Now, if you’re sure the Dates & Times are the important values here (compared to the notes) following one of the examples I’ve shared above, here’s now the Action formula of the button :blush: :

[Notes 3].Filter([Date & Time]!="").FormulaMap(RunActions(AddRow([Notes 3 - Archives],[Notes 3 - Archives].[Date & Time],CurrentValue.[Date & Time],[Notes 3 - Archives].[Stock Symbols],CurrentValue.[Stock Symbols],[Notes 3 - Archives].Notes,CurrentValue.Notes),ModifyRows(CurrentValue,[Date & Time],"",Notes,"")))

In short, the button does 2 things (that’s why I used RunActions() ) :

  1. Copy the appropriate rows from the table [Notes 3] in the table [Notes 3 - Archives] (AddRow())
  2. Clear the Date & Time values and the Notes for the rows it just copied (ModifyRows())

Now, for the longer version :
First the button needs to know which rows it should used to do what it has to do :blush: , in this case the rows where Date & Time are not blank.

:point_right: [Notes 3].Filter([Date & Time]!="")

Now that it knows which rows to use, it does this :

:point_right: [...] FormulaMap(RunActions(AddRow([Notes 3 - Archives],[Notes 3 - Archives].[Date & Time],CurrentValue.[Date & Time],[Notes 3 - Archives].[Stock Symbols],CurrentValue.[Stock Symbols],[Notes 3 - Archives].Notes,CurrentValue.Notes) [...]

In other words :blush: :
For each (FormulaMap()) rows in the list of all rows where the Date & Time are not blank (CurrentValue) add a row in the table [Notes 3 - Archives] with those values :

  • For the field [Notes 3 - Archives].[Date & Time] :point_right: CurrentValue.[Date & Time]
    The current value of the field Date & Time for that specific row
  • For the field [Notes 3 - Archives].[Stock Symbols] :point_right: CurrentValue.[Stock Symbols]
    The current value of the field Stock Symbol for that specific row
  • For the field [Notes 3 - Archives].Notes :point_right: CurrentValue.Notes
    The current value of the field Notes for that specific row

After that, the button does this :

:point_right: [...] ModifyRows(CurrentValue,[Date & Time],"",Notes,"") [...]

and in others words :

Still for for each rows (as it’s still in FormulaMap()) in the list of all rows where the Date & Time are not blank (CurrentValue) modify the value for the Date & Time field and put it as Blank ("") and modify the value for the Date & Time field and put it as Blank ("") too :blush: .

And that’s it I think :blush: .

Hope this helps :wink:

2 Likes

Now I know why I couldn’t figure it out!!!
This is genius…how can I thank you for your kindness?
You are a master teacher…
…and you’ve given me so many other ideas for how to apply these principles!!
Thank you again!
Gregg

1 Like

I’m very glad to know it helped :grin: (and it’s more than enough :wink: … but thank you for the compliment :blush: )!

1 Like