emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Jean Louis <bugs@gnu.support>
To: Greg Minshall <minshall@umich.edu>
Cc: emacs-orgmode@gnu.org
Subject: Re: form-like process for entering rows in an org-mode table?
Date: Wed, 28 Sep 2022 00:41:41 +0300	[thread overview]
Message-ID: <YzNuFexoypSLlNYo@protected.localdomain> (raw)
In-Reply-To: <278048.1658506324@apollo2.minshall.org>

* Greg Minshall <minshall@umich.edu> [2022-07-22 19:14]:
> hi.  does anyone have any code, or know of any existing package, that
> would allow for some sort of form-like (or "transient"-like) interface
> for adding rows to an org-mode table?  in particular, that provides some
> sort of =completing-read= interface for a given set of choices for a
> given column, etc.

I have total understanding for this use case, as I do similar all the
time. Just that I don't use Org tables, I use database tables.

I would complete from database like

(rcd-completing-read-sql-hash "Choose person" "SELECT people_id,
people_name FROM people" cf-db)

That would give me person to choose from.

(defun rcd-completing-read-sql-hash (prompt sql pg &optional history initial-input not-require-match auto-initial-input)
  "Complete selection by using SQL.

First column shall be unique id, followed by text
representation.  Example SQL query:

SELECT people_id, people_firstname || ' ' || people_lastname FROM people

PG is database handle.  HISTORY is supported with INITIAL-INPUT
Argument PROMPT will be displayed to user."
  (let* ((hash (rcd-sql-hash-with-key sql pg))
	 (completion-ignore-case t)
	 (require-match (if not-require-match nil t))
	 (history (or history (rcd-ask-history-variable prompt)))
	 (initial-input (or initial-input (when auto-initial-input (car (symbol-value history)))))
	 (choice (completing-read prompt hash nil require-match initial-input history))
	 (choice (string-trim choice))
	 (id (gethash choice hash)))
    (if id id
      (if not-require-match 

I find parsing text table rather difficult. If you have extensive
work, I would recommend keeping information in the database table, and
then inserting it into Org mode by using source blocks or functions.

Then I am thinking as following:

- psql command line tool has csv format among others
- pandoc can convert from csv to org

and here it is:

$ psql -A -t -X -q -c "select peoplenames_peoplename, peoplenames_peopleid as people from peoplenames where peoplenames_peoplename ~* 'minsh' limit 5;" 
Greg  Minshall|320418

then that CSV may be converted by using pandoc:

psql -A -t -X -q -c "select peoplenames_peoplename, peoplenames_peopleid as people from peoplenames where peoplenames_peoplename ~* 'minsh' limit 5;" | pandoc -f csv -t org
| Greg Minshall|320418 |

then that may be beautified... OK not moving so well, let me try with
HTML to Org:

psql -A -t -H -q -c "select peoplenames_peoplename, peoplenames_peopleid as people from peoplenames where peoplenames_peoplename ~* 'min' limit 5;" | pandoc -f html -t org
| Ali Amini               | 131 |
| Jasmin Hatipović        | 187 |
| Lamin Bangura           | 211 |
| Errol Anthony Flemmings | 259 |
| Carlene Cummins         | 302 |

So there it is, conversion from PostgreSQL to HTML to Org. 500 people
are converted with his timing:

real    0m0.558s
user    0m0.445s
sys     0m0.121s

Let us say I wish to enter new identity from which to send emails,
letters, then I have this function:

(defun rcd-db-table-identities-insert-new-row (&optional id)
  "Add new identity."
  (when-tabulated-id "identities"
      (let* ((name (rcd-ask-get "Identity name: "))
	     (first-name (rcd-ask-get "First name: "))
	     (last-name (rcd-ask-get "Last name: "))
	     (e-mail (rcd-ask-get "E-mail: "))
	     (signature (rcd-ask-or-null "Signature: "))
	     (signature (if signature (sql-escape-string signature) "NULL"))
	     (signature-file (read-file-name "File name: "))
	     (signature-file (if signature-file (sql-escape-string signature-file) "NULL"))
	     (organization (rcd-ask "Organization: "))
	     (organization (if organization (sql-escape-string organization) "NULL"))
	     (account (cf-accounts-select))
	     (language (rcd-db-language-select))
	     (sql (format "INSERT INTO identities (identities_name, identities_firstname, identities_lastname,
                                       identities_email, identities_signature, identities_signaturefile,
                                       identities_organization, identities_accounts, identities_languages)
                  VALUES ($1, $2, $3, $4, %s, %s, %s, $5, $6)"
			  signature signature-file organization)))
	(rcd-sql sql cf-db name first-name last-name e-mail account language)

$ time psql -A -t -H -q -c "select peoplelisttypes_name from peoplelisttypes;" | pandoc -f html -t org
| Competitor                  |
| Integrator                  |
| Investor                    |
| Partner                     |
| Reseller                    |
| Our company                 |
| Accounting                  |
| Mailing List                |
| Mobile Network Provider     |
| Family                      |
| Default                     |
| Managed Company or Business |
| Country                     |
| Company                     |
| Cooperative Society         |
| Business                    |
| Press                       |
| Sales Leads                 |

real    0m0.317s
user    0m0.302s
sys     0m0.021s

For more help let me know. Keeping stuff in the ordered database is useful.


Take action in Free Software Foundation campaigns:

In support of Richard M. Stallman

  reply	other threads:[~2022-09-27 22:00 UTC|newest]

Thread overview: 6+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2022-07-22 16:12 form-like process for entering rows in an org-mode table? Greg Minshall
2022-09-27 21:41 ` Jean Louis [this message]
2022-09-28  3:33   ` Greg Minshall
2022-09-28  9:23     ` Jean Louis
2022-09-29  7:51       ` Greg Minshall
2022-09-29 12:04         ` Quiliro Ordóñez

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=YzNuFexoypSLlNYo@protected.localdomain \
    --to=bugs@gnu.support \
    --cc=emacs-orgmode@gnu.org \
    --cc=minshall@umich.edu \


* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox


This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).