MapInfo Pro

Help me. I have what I think is a really basic query but can't find the answer anywhere! I have a table, with a column 'Area' and a column 'Area_buildings'. I then have an area called 'Total_applicable_area' which I want to auto update to be 'Area' minus 'Area_buildings'.
I just can't seem to write an expression that works in the update columns section. Can't find any help on it either.

Please help! Thanks,

------------------------------
Ian Dunkley
Knowledge Community Shared Account
------------------------------

Hi Ian.  Assuming both columns are numeric, you should be able to use the "Update Column" function and choose the table and the field to update.  Then, in the "Value" dialog area it is probably best to click on "Assist".  Then select your AREA column from the "Columns" drop-down.  Then either type in a - or select it from the "Operators" drop-down.  Then select your "AREA_BUILDINGS" column from the "Columns" drop-down.  This should then show an expression AREA-AREA_BUILDINGS in the "Value" dialog.
Click OK and you should find that your "TOTAL_APPLICABLE_AREA" field has been updated.
Have tried to make this a step-by-step to solve your problem.  You can certainly make the process shorter but this way should guarantee results.

HTH.

------------------------------
Tony Maber
Pitney Bowes Software Pty Ltd (formerly Encom and MapInfo)
North Sydney
------------------------------

Hi @Ian Dunkley,

Here's an article that has screenshots to further illustrate the process that @Tony Maber described. There's a video embedded that walks you through the steps too!

Cheers, Ashley
​

------------------------------
Ashley Crane
Regional Director, Software Support
Pitney Bowes
------------------------------

Original Message:
Sent: 03-25-2019 02:15
From: Tony Maber
Subject: Help - really basic query

Hi Ian.  Assuming both columns are numeric, you should be able to use the "Update Column" function and choose the table and the field to update.  Then, in the "Value" dialog area it is probably best to click on "Assist".  Then select your AREA column from the "Columns" drop-down.  Then either type in a - or select it from the "Operators" drop-down.  Then select your "AREA_BUILDINGS" column from the "Columns" drop-down.  This should then show an expression AREA-AREA_BUILDINGS in the "Value" dialog.
Click OK and you should find that your "TOTAL_APPLICABLE_AREA" field has been updated.
Have tried to make this a step-by-step to solve your problem.  You can certainly make the process shorter but this way should guarantee results.

HTH.

------------------------------
Tony Maber
Pitney Bowes Software Pty Ltd (formerly Encom and MapInfo)
North Sydney
------------------------------

Hi Ian
Is the key aspect of this "auto update"?
The Update Column function is a great way of populating a new field in your database with permanent values but it would need to be run again and again if the data is edited or new records added.
You could add a temporary column to your data using the "Pick Fields" option in the Browser section of the Table ribbon.
From there, select the Expression option and add to your Columns in the Browser. Then use the same expression that @Tony Maber deescribed.
Lastly, you can check the box to "Save As Default Browser View".
This column will then be generated each time you open the table and be populated from the values in the other 2 columns - edited or new.
​

------------------------------
John Ievers
CDR Group
Hope Valley, United Kingdom
------------------------------

Thanks All.

I had tried the update using the expression Area-Area_buildings as that seemed the most obvious. It didn't work. 

It's comforting that someone else has suggested it - means I'm not being daft. 

I suspect I had the columns formatted wrong so it couldn't do the sum. 

I got around it by exporting to csv and doing the sums in Excel, then re importing. 

I'm sorted now, but many thanks for the replies.

Ian

------------------------------
Ian Dunkley
Knowledge Community Shared Account
------------------------------

Original Message:
Sent: 03-26-2019 07:12
From: John Ievers
Subject: Help - really basic query

Hi Ian
Is the key aspect of this "auto update"?
The Update Column function is a great way of populating a new field in your database with permanent values but it would need to be run again and again if the data is edited or new records added.
You could add a temporary column to your data using the "Pick Fields" option in the Browser section of the Table ribbon.
From there, select the Expression option and add to your Columns in the Browser. Then use the same expression that @Tony Maber deescribed.
Lastly, you can check the box to "Save As Default Browser View".
This column will then be generated each time you open the table and be populated from the values in the other 2 columns - edited or new.
​

------------------------------
John Ievers
CDR Group
Hope Valley, United Kingdom
------------------------------

Hi Ian.  Thought that might be the case.  That is why at the start of my suggestion I said "assuming both columns are numeric".

Glad to know you've confirmed you're not daft  :-)


Tony

------------------------------
Tony Maber
Pitney Bowes Software Pty Ltd (formerly Encom and MapInfo)
North Sydney
------------------------------

Original Message:
Sent: 03-26-2019 07:58
From: Ian Dunkley
Subject: Help - really basic query

Thanks All.

I had tried the update using the expression Area-Area_buildings as that seemed the most obvious. It didn't work.

It's comforting that someone else has suggested it - means I'm not being daft.

I suspect I had the columns formatted wrong so it couldn't do the sum.

I got around it by exporting to csv and doing the sums in Excel, then re importing.

I'm sorted now, but many thanks for the replies.

Ian

------------------------------
Ian Dunkley
Knowledge Community Shared Account
------------------------------


Original Message:
Sent: 03-26-2019 07:12
From: John Ievers
Subject: Help - really basic query

Hi Ian
Is the key aspect of this "auto update"?
The Update Column function is a great way of populating a new field in your database with permanent values but it would need to be run again and again if the data is edited or new records added.
You could add a temporary column to your data using the "Pick Fields" option in the Browser section of the Table ribbon.
From there, select the Expression option and add to your Columns in the Browser. Then use the same expression that @Tony Maber deescribed.
Lastly, you can check the box to "Save As Default Browser View".
This column will then be generated each time you open the table and be populated from the values in the other 2 columns - edited or new.
​

------------------------------
John Ievers
CDR Group
Hope Valley, United Kingdom