Augmenting table to make a database within NWP

[greenmorpher]

Hello Martin

I've recently been using NWP to build a tab/comma separated database and it has given me to furiously to think. Actually, I have got furiously to think only today after mindlessly building several databases with many hundreds of entries as simple text documents with comma separated text. This was a bit painful, I have to say. This morning, I saw the dawn twice -- a bright one inside my head on how to do those databases better and a gray, Melbourne winter dawn outside the window.

Set up a table for entry. Ah! That's better! I can't believe that I didn't do that in the first place, but there you go -- wanting a comma or tab separated plain text document as the final format for transfer to other applications so who on earth would consider putting in an intermediate step rather than go to a plain text document in the first place? Probably everyone else.

I can see what's going on and where my cursor is located so much more easily with the table, and most importantly, I don't have to insert the entry separators and therefore insert typos like periods instead of commas (tabs make for such messy text to view when you have entries of varied length), and count the commas when I have blank fields. At the end of the day, I can simply copy the text and drop it into the target app as tab separated text. Great.

I've set up a keyboard shortcut to create a new row in addition to the new row being created just by tabbing forward, but it would be better if that was done with a return to avoid possible problems with, e.g. starting a new record by mistake within a current record which should have had a blank field at the end, which would put everything entered subsequently out of kilter. Actually, this probably would be good for ordinary table use.

Some selection options would be good. "Select row", "Select column" exist, "Select whole table" and some range selections would add to utility. Obviously, these can be done with the mouse but on a multipage database, it would be more convenient to be able to do these things with a click on a menu item or a keyboard shortcut.

What about finding empty fields? In a table the tabs move the cursor from cell to cell and when exported, the cells are separated by tabs and rows/records by returns. But within the table, finding an empty field, which in the text would be two tabs together, don't do nuffin. Is there a code for that so that empty cells could be found and selected in the normal way in NWP? What about a command to find whatever and select the row/record or find all of whatever and select all the rows/records? A macro?

Some sort of provision to avoid accidental deletion would be advantageous too. Data in databases is so precious. So what about provision for a confirmation of deletion of material within a database, and the same thing with bells, whistles, and a message in CAPS saying "ARE YOU SOME SORT OF IDIOT??? THIS IS A DATABASE, YOU TWIT!!!" when we try to delete a whole database?

Copying the sort commands (Edit menu) over to the Table menu and retitling them "Sort rows" would also be tidy. So they're duplicating? So what! When you're in a table you expect to use the Table menu and Palette for Table type activities. Besides, it is not clear until you do it that "Sort paragraphs" is actually "Sort rows" in a table because you're not using a return to go from row to row.

And finally, two things we've talked about before on this forum and elsewhere and you've taken on board, I believe, provision for nailing down the table set up so it can't be altered by accident, plus provision to set it up by measurement.

Oops -- one other nasty little thing that really gets up my nose. Exporting stuff in that stupid card thingy format in addition to the time honoured tab/comma separated text. Seems an increasing number of database type apps want these card thingy format.

Anyone else interested in such developments? Got a better idea?

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or http://www.worsleypress.com

[martin]

Hi Geoff,

Wow, that's quite a post you have there! Let's work through some of that:

I've set up a keyboard shortcut to create a new row in addition to the new row being created just by tabbing forward, but it would be better if that was done with a return to avoid possible problems with, e.g. starting a new record by mistake within a current record which should have had a blank field at the end, which would put everything entered subsequently out of kilter.

I'm not sure I understand what you're describing. You want the return key to create a new table row?

Some selection options would be good. "Select row", "Select column" exist, "Select whole table" and some range selections would add to utility. Obviously, these can be done with the mouse but on a multipage database, it would be more convenient to be able to do these things with a click on a menu item or a keyboard shortcut.

As you know, you can assign any menu any keyboard shortcut you please, so I'm not sure I know what you're asking exactly. You can also use the arrow keys to create a multi-cell selection, eg: hold down Shift and then press some arrow keys to extend the cell selection across cells.

What about finding empty fields?
...
Is there a code for that so that empty cells could be found and selected in the normal way in NWP? What about a command to find whatever and select the row/record or find all of whatever and select all the rows/records? A macro?

One could definitely write a macro for this. I can't write one now, but perhaps tomorrow.

Copying the sort commands (Edit menu) over to the Table menu and retitling them "Sort rows" would also be tidy. So they're duplicating? So what! When you're in a table you expect to use the Table menu and Palette for Table type activities. Besides, it is not clear until you do it that "Sort paragraphs" is actually "Sort rows" in a table because you're not using a return to go from row to row.

That's not a bad idea. We've also had it on the "to do" list to add the ability to sort by any column. Currently rows are always sorted by the first column.

Exporting stuff in that stupid card thingy format in addition to the time honoured tab/comma separated text. Seems an increasing number of database type apps want these card thingy format.

I don't think I know what format you're referring to. There's vcards for contact information, but I think that's all they store.

[greenmorpher]

Hiya Martin

Wow, that's quite a post you have there! Let's work through some of that:

I've set up a keyboard shortcut to create a new row in addition to the new row being created just by tabbing forward, but it would be better if that was done with a return to avoid possible problems ...

I'm not sure I understand what you're describing. You want the return key to create a new table row?

In a word, yes!!! Which would involve using some variation on return in text within cells.

Some selection options would be good. "Select row", "Select column" exist, "Select whole table" and some range selections would add to utility. Obviously, these can be done with the mouse but on a multipage database, it would be more convenient to be able to do these things with a click on a menu item or a keyboard shortcut.

As you know, you can assign any menu any keyboard shortcut you please, so I'm not sure I know what you're asking exactly.

I'm thinking of: "Select Row (...fill in number...) to Row (...fill in number...)".

What about finding empty fields? What about a command to find whatever and select the row/record or find all of whatever and select all the rows/records? A macro?

One could definitely write a macro for this. I can't write one now, but perhaps tomorrow.

That's a bit fast, actually, I don't need it yet -- but I certainly will in future!

We've also had it on the "to do" list to add the ability to sort by any column. Currently rows are always sorted by the first column.

Hey, that would be pretty cool too.

Exporting stuff in that stupid card thingy...

I don't think I know what format you're referring to. There's vcards for contact information, but I think that's all they store.

Vcards, that's the one. We have V-Line here (it being Victoria and all we have having some remnant country railway lines), so I always get confused. Yes, vcards seems to be growing in favor as a way of moving contact information around. Stupid.

Well, that all sounds good, Martin, many thanks for getting back so quickly and so encouragingly. Not forgetting to nail the table to the floor so it is difficult to delete it when I fall asleep with my head on the keyboard (others must surely do this too -- data entry is soooooo boring!) and as usual, I totally disdain the notion that some others might have that I should mention vertical rulers or, indeed, anything vertical at all (given that I have just admitted falling asleep at the computer with my head in the keyboard).

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or http://www.worsleypress.com

[greenmorpher]

I've just been working on my "table as database" and I've struck something odd.

I have some data I entered as just lines of comma separated text. (Boring but have you looked at any TV lately?)

So I did a Replace All to change the commas to tabs, then tried dropping the tab separated text into the existing table. Nope. It all fell into one cell.

So I selected the text, converted it to table, then copied that stuff out of the table and put it in the new table. That worked!

There is a need to be able to add many rows in the Table palette just by typing in a number. As it is, you just have to sit there with your colick on the little arrow. It works fast, but it's not ideal.

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com

[Kino]

Hiya Geoff,

"Select row", "Select column" exist, "Select whole table" and some range selections would add to utility.

“Select whole table” is :Table:Select:Table. Or is it not what you are looking for?

What about finding empty fields?

If I understand you exactly, the following macro will do the job.

Code: Select all

if Menu Enabled ':Table:Select:Table'
	Menu ':Table:Select:Table'
	Find All in Selection '\p{Any}+', 'E'
	Menu ':Edit:Select:Invert Selection'
else
	exit 'No selection in a table, exiting...'
end

There is a need to be able to add many rows in the Table palette just by typing in a number.

I second this. That would be a nice enhancement. In the meantime, here is a macro working in a similar way.

Code: Select all

if Menu Enabled ':Table:Select:Table'
	$i = $j = Prompt Input 'Add # rows', '', '', '3'
	$i = Cast to Int $i  # $i will remain the same if it is an integer
	if $i == $j
		while $i
			Menu ':Table:Insert:Row Below'
			$i -= 1
		end
	else
		exit "Invalid input: \"$j\", exiting..."
	end
else
	exit 'No selection in a table, exiting...'
end

[greenmorpher]

Magic, Kino. Many thanks. They both work perfectly. Looking at the one to select empty cells in action, it seems that you are selecting all the text then inverting that selection. Yes? Oh, I just read the macro. Exactly!!!

When I was talking about finding the empty cells, I was talking faster than I was thinking. I was actually thinking of finding them within the Find/Replace process so that it could be applied to a selection of cells or to all the cells -- thus finding a limited number of empties, e.g. empties in a particular column or part of a column, or within a selection, then filling those empties with something using Replace.

I suspect that would make things rather too complex. Since a selection is already in existence, the find actually has to be on whatever it is that marks a cell. In the plain text, is it two adjoining tabs, of course ... I suppose there would be a possibility of cutting the text only out of the table so that the two tabs were copied, then replacing all tab-tab with tab-content-tab, then plugging the text back in where it came from.

Oh wait -- but if you cut whole rows of content, you cut the row out of the table too. Hmmm!

Thanks for pointing out Select>Table. Maybe it needs to be labelled "WHOLE Table" for people like me.

Must go -- I have some allegedly Irish soda bread in the over and it is about ready.

Many thanks again, Kino.

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com

[Kino]

Exactly!!!

Exactly! ;-)

I was actually thinking of finding them within the Find/Replace process so that it could be applied to a selection of cells or to all the cells -- thus finding a limited number of empties, e.g. empties in a particular column or part of a column, or within a selection, then filling those empties with something using Replace.

Ah, I see. How about this one? It finds and selects empty cells in selected cells. If just a single cell of a table is selected or if the caret is in a table cell without making a visible selection, it finds and selects all empty cells in the table. If you want it to find and select empty cells in a given row/column, just select the row/column using :Table:Select:Rows or Columns before running the macro.

Code: Select all

$doc = Document.active
if $doc == undefined
	exit 'No open document, exiting...'
end

$tblSels = $doc.tableSelections
if ! $tblSels.count
	exit 'No selection in a table, exiting...'
end

$selsByTable = Hash.new
foreach $tblSel in $tblSels
	if $selsByTable{$tblSel.table} == undefined
		$selsByTable{$tblSel.table} = Array.new
	end
	$selsByTable{$tblSel.table}.appendValue $tblSel
end

$sels = Array.new
foreach $table in $selsByTable.keys
	foreach $tblSel in $selsByTable{$table}
		$rows = $tblSel.rowRange
		$cols = $tblSel.columnRange
		if $selsByTable{$table}.count == 1
			if $rows.length == 1
				if $cols.length == 1
					$rows = Range.new 0, $table.rowCount
					$cols = Range.new 0, $table.columnCount
				end
			end
		end
		$r = $rows.location
		$c = $cols.location
		while $r < $rows.bound
			while $c < $cols.bound
				$text = $table.textAtRowAndColumn $r, $c
				if ! $text.length
					$row = Range.new $r, 1
					$col = Range.new $c, 1
					$sel = TableSelection.new $table, $row, $col
					$sels.appendValue $sel
				end
				$c += 1
			end
			$c = $cols.location
			$r += 1
		end
	end
end

if ! $sels.count
	exit 'No empty cell found, exiting...'
end

$doc.setSelections $sels

Edit: I fixed a bug in the macro. It did not work properly when there are multiple selections in the same table and one of them consists of a single cell.

[greenmorpher]

Whoa!!!

A tour de force, Kino. Just great. Many thanks -- that does just as want.

And for selecting all empty cells, this latest macro is virtually instantaneous.

Now, is the following a step too far?

When pasting rows into a table, it would be desirable for table to generate enough new rows to accommodate the number of rows being created.

When shifting rows around by copying and pasting material into a table, it is necessary to frirst insert the number of empty rows required. Errors are can occur! If you paste in without enough empty rows to accommodate the number of rows being pasted in, then two results are possible:

* If pasting among existing rows, any excess rows will be pasted in by deleting the same number of existing rows under the paste-in row. So say I am pasting 5 rows into the middle of a table, and I have inserted only 4 empty rows, then the fifth row being pasted in will overwrite and delete the first row below the 4 blank rows. Obviously, for such a small number, anyone (even me!) would notice, but with larger numbers of rows being pasted, a row or three being deleted might not be immediately obvious.

* If the pasting is at the end of a table, then the rows in excess of the number of empty rows available will simply not be pasted in. Again, the error might be missed.

In addition, of course, it would be much more convenient to simply insert a row then paste and have the required number of rows generated automatically.

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com

[greenmorpher]

I went and looked in the MANUAL and located auto paragraph numbering in the Line Numbers palette.

Hey, Martin -- that's something else that could well be duplicated in the Tables menu/palette, but under the guise of Row Numbers.

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com

[greenmorpher]

Hiya Martin

Looks as though I have a bug here.

I have set command-arrow (up, down, left, right) to create a new row or column respectively.

BUT while command-down arrow works fine, and without the command key, the arrow moves around the text and cells in the normal way, the other three, up, left, right create a new row or column just by hitting the arrow, without using the command key.

A touch off-putting!

sgCheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com

[martin]

Looks as though I have a bug here.

I have set command-arrow (up, down, left, right) to create a new row or column respectively.

BUT while command-down arrow works fine, and without the command key, the arrow moves around the text and cells in the normal way, the other three, up, left, right create a new row or column just by hitting the arrow, without using the command key.

As yes, that is a little too touchy. I actually didn't know one could override Command + Arrow Key using the NWP menu key preferences. I'll file the bug, thanks.

[Kino]

When pasting rows into a table, it would be desirable for table to generate enough new rows to accommodate the number of rows being created. [ . . . ] Errors are can occur!

Those oddities occur when the number of columns of the copied table cells is different from that of the destination table, it seems. If they are the same, those table cells will be inserted just after the caret or selection in the destination table without any data lost from the copied table cells or the destination table.

The macro below will add empty columns to the copied table cells or to the destination table if their numbers of columns are different. Try to use it instead of Paste (command-v) after copying table cells and putting the caret in the destination table.

Code: Select all

$doc = Document.active
if $doc == undefined
	exit 'No open document, exiting...'
end

$destTableSel = $doc.tableSelection
if $destTableSel == undefined
	exit 'No selection in a table, exiting...'
end
$destTable = $destTableSel.table
$destC = $destTable.columnCount

$clip = Read Clipboard
$temp = Document.newWithText $clip
Select Document Start
Select Next Table
$srcTableSel = $temp.tableSelection
if $srcTableSel == undefined
	exit 'No table in the clipboard, exiting...'
	$temp.close true
end
$srcTable = $srcTableSel.table
$srcC = $srcTable.columnCount

$d = $srcC - $destC
if $d < 0
	while $d < 0
		Menu ':Table:Insert:Column to the Right'
		$d += 1
	end
	Menu ':Table:Select:Table'
	Menu ':Edit:Copy:Copy'
end
$temp.close true

Document.setActive $doc
if $d > 0
	Menu ':Table:Select:Table'
	while $d > 0
		Menu ':Table:Insert:Column to the Right'
		$d -= 1
	end
	$doc.setSelection $destTableSel
end

Menu ':Edit:Paste:Paste'

[greenmorpher]

Aargh -- Kino, I am sorry, I have sent you on a wild goose chase here.

As you surmise, I was copying only partial rows.

In fact, I don't actually need to do that -- I can just copy whole rows (records) including their empty fields (columns) which in this case are on the left and everything works fine. As you say, when whole rows are inserted, table creates new rows for them. It doesn't even matter where in a row your caret is located in a row when you paste -- with whole rows being pasted, table gets busy inserting new rows below the row where the caret is located.

Learning all the time, but trying not to be too stupid and giving grief to friends.

Cheers, Geoff

Geoffrey Heard
Publisher, Editor, Business Writer
The Worsley Press

FREE Bonus book offer. Get "How to make great ads for (sm)all business" FREE when you buy "Type & Layout: Are you communicating or just making pretty shapes?" or "How to Start and Produce a Magazine or Newsletter". Amazon or www.worsleypress.com