Re: Spreadsheet FR

[Matt Lundin <mdl@imapmail.org>]

Hi Russell,

Russell Adams <rladams@adamsinfoserv.com> writes:

> I adore the text spreadsheet, however there's one feature Excel
> provides which I don't have in org.
>
> I often use Excel for "lists", where I can sort or narrow the data by
> specific criteria from a larger list.
>
> Would it be feasible to "narrow" a table by criteria on a specific
> field in between separators? Ie: only display those cells in field A
> if they are > 2, or if field B matches "Pick Me!".

One way to achieve this behavior is by using properties + Eric Schulte's
org-collector.el in contrib. You can enter the data in a subtree using
properties and columns and then capture a subset of that data in a table
using a dynamic block. E.g., given the following subtree...

--8<---------------cut here---------------start------------->8---
* My shopping list
  :PROPERTIES:
  :COLUMNS:  %40ITEM %10AMOUNT %10TYPE
  :ID:       41101c80-85b9-4af6-8138-8ad460a2949e
  :END:
** Bananas
   :PROPERTIES:
   :AMOUNT:   2.65
   :TYPE:     fruit
   :END:
** Cheese
   :PROPERTIES:
   :AMOUNT:   4.54
   :TYPE:     dairy
   :END:
** Flour
   :PROPERTIES:
   :AMOUNT:   3.00
   :TYPE:     grain
   :END:
** Dish soap
   :PROPERTIES:
   :AMOUNT:   1.29
   :TYPE:     kitchen
   :END:
** Lettuce
   :PROPERTIES:
   :AMOUNT:   1.80
   :TYPE:     vegetable
   :END:
** Carrots
   :PROPERTIES:
   :AMOUNT:   2.56
   :TYPE:     vegetable
   :END:
** Milk
   :PROPERTIES:
   :AMOUNT:   3.25
   :TYPE:     dairy
   :END:
--8<---------------cut here---------------end--------------->8---

...you can use org-collector to capture a subset of data as follows...

--8<---------------cut here---------------start------------->8---
* Data

#+begin: propview :id "41101c80-85b9-4af6-8138-8ad460a2949e" :conds ((string= TYPE "vegetable")) :cols (ITEM AMOUNT TYPE)
| "ITEM"    | "AMOUNT" | "TYPE"      |
|-----------+----------+-------------|
| "Lettuce" |      1.8 | "vegetable" |
| "Carrots" |     2.56 | "vegetable" |
|-----------+----------+-------------|
|           |     4.36 |             |
#+TBLFM: $LR2=vsum(@I+1..@II-1)
#+end
--8<---------------cut here---------------end--------------->8---

Change the conditions to match dairy items greater than 4 and you get
the following:

--8<---------------cut here---------------start------------->8---
* Data

#+begin: propview :id "41101c80-85b9-4af6-8138-8ad460a2949e" :conds ((and (string= TYPE "dairy") (> AMOUNT 4))) :cols (ITEM AMOUNT TYPE)
| "ITEM"   | "AMOUNT" | "TYPE"  |
|----------+----------+---------|
| "Cheese" |     4.54 | "dairy" |
|----------+----------+---------|
|          |     4.54 |         |
#+TBLFM: $LR2=vsum(@I+1..@II-1)
#+end
--8<---------------cut here---------------end--------------->8---

Best,
Matt