Re: form-like process for entering rows in an org-mode table?

[Jean Louis <bugs@gnu.support>]

* 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 
	  choice))))

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."
  (interactive)
  (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)
	(rcd-tabulated-refresh))))

$ 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.


-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/