Subscribe to this thread
Home - General / All posts - M9. Under what circumstances can I add field to a table, * asterisk at right hand end
tonyw
736 post(s)
#16-Jan-19 17:08

In the user manual, in the section Editing Tables,

If a table supports adding new fields we can do so right from a table window with no need to open the table's schema via Edit - Schema. Tables that allow adding fields will be displayed with a blank, asterisk *field

I've checked a selection of my Tables and none have an asterisk in the last column. My understanding from the manual is if I click on the asterisk to add a new field, I also have access to Set Field Value which will open up the Expression Builder. However I don't see the asterisk at the right hand end of the field headings in my Tables. The only way I can add new fields is to use Edit > Schema where if I add a new Geom field, I don't have access to the expression builder.

My work flow is to gain access to the expression builder when adding a new field:

1. Transform > Expression. Build my expression using an existing field as the destination BUT don't update the field. I just create the expression and visually see that the results appear correct but I don't commit the results to the arbitrary target field. I copy the expression and abandon the Transform dialog.

2. Go to Edit > Schema, add my new field and paste the expression into the Expression box, click Add then OK. It would be handy if I can access the expression builder directly when creating a new field.

Thanks. Maybe I'm missing something.

tjhb
10,094 post(s)
#16-Jan-19 17:58

This has come up before (more than once I think). I seem to remember there are plans to give access to the Expression Builder directly from the Schema dialog, as part of a pending redesign.

tonyw
736 post(s)
#16-Jan-19 21:49

Thanks Tim, I recall that discussion about accessing the expression builder directly from the Edit > Schema dialog.

Have you seen instances where an asterisk is available in the last, blank field header on the right of a table that would let you add a new field?

tjhb
10,094 post(s)
#16-Jan-19 22:01

I think that design has gone. I just checked 9.0.164.0 (Manifold 9 pre-release, the oldest I have access to), and it had already gone by then, so I think it must have been only Radian or pre-Radian beta. I don't know.

I imagine the reason it went (though perhaps not many people used it) is that the overhead of adding one field at a time is quite high. Better to make changes in Edit > Schema then apply them all together.

tonyw
736 post(s)
#16-Jan-19 22:07

Ah, OK. The asterisks is handy to add new records, would be handy for adding new fields.

Dimitri


7,413 post(s)
#17-Jan-19 07:02

That's an error in the documentation, a topic that has not been correctly update to handle changes. The Example it refers to was updated but not the text in that topic. Besides that text the illustrations need to be updated as well. I've submitted a bug report.

That change occurred a long time ago, when the new grid control came into use for tables. Adding a record to a table is no big deal, but changing the structure of a database, which is what you're doing when you add a column to a table, is potentially a very big deal, so that's why it is done in the Schema dialog. [The Schema dialog needs to be, and will be, improved, but that's a different discussion...]

Dimitri


7,413 post(s)
#17-Jan-19 11:30

Fixed.

adamw


10,447 post(s)
#17-Jan-19 07:20

Adding a little to what Dimitri says above:

We used to have the same process for adding a new field and setting field values. We separated these processes because we felt this makes the UI better.

Two examples:

(1) When you are adding a field, you frequently want to see what other fields you already have (because maybe you already added the field you want to have, but with a slightly different type / slightly different name, or because you want to use the exact same type as some other field, etc). If we combine the process of adding a field with the process of setting field values, there is not a lot of room for controls that show you what other fields you have, what types they are, etc.

(2) Right now when we are talking about the UI for setting field values, we are mostly talking about the Transform pane. We are planning to add more tools which set field values using different logic. Trying to allow either using existing fields or creating new fields for such new tools just feels forced and not terribly useful.

Sum total, add field first (eg, using Edit - Schema) and populate it second. (Although all of the above said, we can perhaps still display the * header for a new field and invoke Edit - Schema or a simpler dialog when you click on it. We'll see.)

tonyw
736 post(s)
#17-Jan-19 17:33

and populate it second.

I have two ways of populating a new field. I prefer the first way because it is "persistent", the expression is built into how the field works and recalculates and updates on the fly. The second way below, I need to rewrite a new expression every time to update the field after a change to the table.

Method 1. Preferred.

- Open table, go to Contents > Transform > Expression

- Build my expression which 95% of the time is rounddecs(GeomArea([Geom],0)/10000,0)

- Copy the expression and then abandon the expression

- Click Edit > Schema, create my new geom field, name it, and paste in my expression, add and click OK.

Now the expression is persistent and the field recalculates everytime I change the table.

Method 2. Faster initially, requires re-work every time I change the table.

- Click Edit > Schema, create my new geom field

- go to Contents > Transform > Expression, select the new field as the destination

- Build my expression which 95% of the time is rounddecs(GeomArea([Geom],0)/10000,0)

- Update the field

When I change the table I need to do the transform > expression again to refresh the calculation. So method 2 is faster (for me) initially but requires rework and I run the risk of forgetting to update the calculations.

Could there be an option to make an expression persistent in a field if built through Transform?

perhaps still display the * header for a new field and invoke Edit - Schema

PS. Yes, if the asterisk was a short cut to the Edit > Schema dialag box, that would be nice.

tjhb
10,094 post(s)
#17-Jan-19 20:18

Method 1 is a computed field. It has code behind it, its values are dynamic. Method 2 is an ordinary or static field.

For a computed field, as you say,

the expression is built into how the field works

and the difference is decided when the field is created. You can't later convert a computed field into a static field, or vice versa. Nor can you manually change values in a computed field--you can't update or transform it.

So I think it is natural for the Transform panel to target only static fields (although it can use computed fields as parameters and in expressions), and for computed fields to be created only in the Schema dialog (or in equivalent SQL--see below).

I expect that the Transform panel could be changed so that it could optionally target a <new field>, computed or static, as long as there were also a means to specify its type. That might be a good idea, I don't know. My first impression is that the duplication of function would add confusion, and that it is clearer to keep the roles of the Schema dialog and the Transform panel distinct.

But assuming that their roles should remain distinct, that doesn't mean they should not share some controls. On the contrary, similarity between dialogs can make things more intuitive for frequent users (muscle memory) and help new users. Manifold 9 already does this in many places, and it's great design.

We have discussed the possible addition of an expression builder to the Expression control in Edit > Schema. I still think that is a good idea, and possibly the simplest way to make a workflow like yours more convenient. Maybe that will come when other planned changes are brought through.

For now though, maybe the best way you can smooth your workflow is to use some handy SQL to add a frequently-used computed field wherever you need to.

my expression which 95% of the time is rounddecs(GeomArea([Geom],0)/10000,0)

To add that computed field to [Drawing Table] in SQL, you would open an SQL command window (Ctrl-~) or create a new query, find the place where you have saved this snippet of code, copy and paste, adjust the table name...

ALTER TABLE [Drawing Table] (

  ADD [area (ha)] FLOAT64

    AS [[ RoundDecs(GeomArea([Geom], 0) / 10000, 0) ]]

  );

...then press F5 and you're done.

If you need to do this to more than one table, paste the snippet N times, adjust the table name for each statement, then hit F5, and it is done N times.

(And of course you can use the query builder here if you need to check SQL syntax or make edits.)

tonyw
736 post(s)
#18-Jan-19 00:38

We have discussed the possible addition of an expression builder to the Expression control in Edit > Schema. I still think that is a good idea, and possibly the simplest way to make a workflow like yours more convenient.

Thanks Tim,

Yes, I recall the discussion. Access to the expression builder in Edit > Schema would help me add computed fields easier.

adamw


10,447 post(s)
#18-Jan-19 08:07

We are planning to add an expression builder to Edit - Schema, yes.

We are also considering storing several last expressions you used and allowing you to quickly recall them - because why not, it feels natural and useful. It would help in your case since you are saying that you keep using the same expression over and over.

What you can try to do right now to avoid first composing the expression in the Transform pane and then copy / pasting it into the Edit - Schema dialog is to: compose the expression in the Transform pane, then press Edit Query and adjust the query from:

--SQL9

UPDATE (

  SELECT [mfd_id],

    [AREA],

 RoundDecs(GeomArea([Geom], 0) / 10000, 0) AS [n_AREA]

  FROM [states Table]

  THREADS SystemCpuCount()

SET [AREA] = [n_AREA];

...to:

--SQL9

ALTER TABLE [states Table] (

  ADD [n_Area] FLOAT64 AS [[

 RoundDecs(GeomArea([Geom], 0) / 10000, 0)

  ]]

);

...then run the query.

That is, you use whatever expression the Transform pane built (bolded) and just change the surroundings. This is similar to what Tim suggests, but you can use the Transform pane to preview what the expression will do.

tonyw
736 post(s)
#18-Jan-19 17:46

Thanks Adam for the SQL code.

We are also considering storing several last expressions you used and allowing you to quickly recall them

A couple of other ideas along those lines. In M8, it was handy being able to save thematic formatting. Maybe an option to have a disk icon to save whatever is in the expression builder dialog box? This is essentially what I do now, copy and paste into a MS-Word document along with notes to myself. Similarly, in this idea there would be a file open icon to bring back into the expression builder box whatever was previously stored operating much like thematic formatting settings in M8.

Second idea is to store whatever is in the Expression Builder dialog much like how links to data sources under File > Create > New Data Source. I was pleased for instance WMS links I added were persistent between sessions of M9 and not just stored with the individual .map file. So the idea is to have the option to store whatever is in the expression builder in M9 and have whatever is stored to be persistent between sessions of M9.

Of the two ideas, the first would serve my needs just fine to quickly reuse expressions.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.