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

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

[Greg Minshall]

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.

cheers, Greg

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

[Jean Louis]

* 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/

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

[Greg Minshall]

Jean Louis,

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

the project that needs this has been on the back-burner for a while,
but, for the record, i have looked at "Column View" as a possible
replacement for "org tables as a database".  in addition to a
completing-read interface, column view gives the opportunity to add
descriptive text to a row -- i.e., just the regular, non-property,
content under a headline -- which is very nice.  but, i haven't played
with it enough to see how well it will fit my "needs".

cheers, Greg

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

[Jean Louis]

* Greg Minshall <minshall@umich.edu> [2022-09-28 06:34]:
> Jean Louis,
> 
> > * 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.
> 
> the project that needs this has been on the back-burner for a while,
> but, for the record, i have looked at "Column View" as a possible
> replacement for "org tables as a database".  in addition to a
> completing-read interface, column view gives the opportunity to add
> descriptive text to a row -- i.e., just the regular, non-property,
> content under a headline -- which is very nice.  but, i haven't played
> with it enough to see how well it will fit my "needs".

I do not know what is column view. But I understand that you need
 descriptive text.

What I know is that without unique ID, there can't be conclusive
completion.

That is why I first construct various completions like this:

;; because there can be same names in different rows
(let* ((list '("Greg [1]" "Greg [2]"))
       (choice (completing-read "Choose: " list)))
  (rcd-get-bracketed-id-end choice)) ⇒ 2

(defun rcd-get-bracketed-id-end (s)
  "Return the ID number in string S from within first brackets on its
end. For example it would return 123 from `Some string [123]'"
  (let* ((match (string-match "\\[\\([[:digit:]]*\\)\\][[:space:]]*$" s)))
    (when match
      (string-to-number
       (substring-no-properties s (match-beginning 1) (match-end 1))))))
                                       
-- 
Jean

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

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

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

[Greg Minshall]

hi, Jean Louis,

> I do not know what is column view. But I understand that you need
>  descriptive text.

that was my state until recently, though i had seen various references
to it and its usage on the mailing list.  it at least offers some form
of completing read, at least for what might be in other contexts a "drop
down list".

it's probably at least glancing at to see if it might suit your need.

cheers, Greg

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

[Quiliro Ordóñez]

El 2022-09-29 02:51, Greg Minshall escribió:
> hi, Jean Louis,
> 
>> I do not know what is column view. But I understand that you need
>>  descriptive text.
> 
> that was my state until recently, though i had seen various references
> to it and its usage on the mailing list.  it at least offers some form
> of completing read, at least for what might be in other contexts a "drop
> down list".
> 
> it's probably at least glancing at to see if it might suit your need.

It is not clear what your question is.  Have you solved your problem? 
If you have, please post a message describing how you did it.