MapInfo Pro

 View Only
  • 1.  Help - really basic query

    Posted 03-24-2019 06:43
    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
    ------------------------------


  • 2.  RE: Help - really basic query

    Posted 03-25-2019 02:16
    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
    ------------------------------



  • 3.  RE: Help - really basic query

    Posted 03-25-2019 19:01
    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
    ------------------------------



  • 4.  RE: Help - really basic query

    Posted 03-26-2019 07:12
    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
    ------------------------------



  • 5.  RE: Help - really basic query

    Posted 03-26-2019 07:58
    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
    ------------------------------



  • 6.  RE: Help - really basic query

    Posted 03-26-2019 16:48
    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
    ------------------------------