[Qgis-user] Updating a field set with default global variable on edit
Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi! I've made the same things with triggers in the database. To avoid getting same username I've chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För McDonaldR Skickat: den 22 augusti 2017 13:12 Till: qgis-user@lists.osgeo.org Ämne: [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi too, Well logging into the QGIS-project should really be a big deal right ? - You only have to log in once no matter how many layers are loaded from the database. I have a table inside my QGIS project where I can create new users inside the database and administer reading/writing/admin-privileges. Mvh. / Best regards Casper Bertelsen On 22/08/2017 13:34, Karl Magnus Jönsson wrote: Hi! I’ve made the same things with triggers in the database. To avoid getting same username I’ve chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. *Karl-Magnus * *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *McDonaldR *Skickat:* den 22 augusti 2017 13:12 *Till:* qgis-user@lists.osgeo.org *Ämne:* [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "*created_by*" and "*updated_by*" fields. These are updated by a trigger in the database with the "*current_user*" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - *user_account_name* and *user_full_name* that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "*updated_by*" field to be updated with the current "*user_account_name*". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t: 01307 476419* This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Updating a field set with default global variable on edit
No, it's no big deal. That's why we are using it. And it's only one time per qgis session. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Casper Bertelsen Skickat: den 22 augusti 2017 14:34 Till: qgis-user@lists.osgeo.org Ämne: Re: [Qgis-user] Updating a field set with default global variable on edit Hi too, Well logging into the QGIS-project should really be a big deal right ? - You only have to log in once no matter how many layers are loaded from the database. I have a table inside my QGIS project where I can create new users inside the database and administer reading/writing/admin-privileges. Mvh. / Best regards Casper Bertelsen On 22/08/2017 13:34, Karl Magnus Jönsson wrote: Hi! I've made the same things with triggers in the database. To avoid getting same username I've chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För McDonaldR Skickat: den 22 augusti 2017 13:12 Till: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Ämne: [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Updating a field set with default global variable on edit
Thanks Karl-Magnus We use triggers in the database already and they work perfectly but if we want to record the specific user creating and editing features then we'd have to create separate projects for each user. At the moment the team of about 10 users all use a set of centrally stored project files - one for each different task. I found this post on Stackexchange (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) where global variables are used to populate fields when features are added. However, as Matthias notes in the comments this doesn't work on feature "update". If this were possible then it would meet all our needs. Maybe something that could be funded? Ross From: Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] Sent: 22 August 2017 12:34 To: McDonaldR; qgis-user@lists.osgeo.org Subject: SV: [Qgis-user] Updating a field set with default global variable on edit Hi! I've made the same things with triggers in the database. To avoid getting same username I've chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För McDonaldR Skickat: den 22 augusti 2017 13:12 Till: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Ämne: [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi Ross, If you make your project without saving username and password, the project will prompt for a user and password when opened. Then each member can login using their assigned login and thus making it possible to use current_user in postgres. To remove saved username and password from your current project file (make a copy, beforehand), you can open it in a text editor and seach for untill you find dbname='xx' host=yy port=5432 four lines above and remove the username and password-part only . Mvh. / Best regards Casper Bertelsen On 22/08/2017 15:36, McDonaldR wrote: Thanks Karl-Magnus We use triggers in the database already and they work perfectly but if we want to record the specific user creating and editing features then we’d have to create separate projects for each user. At the moment the team of about 10 users all use a set of centrally stored project files – one for each different task. I found this post on Stackexchange (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) where global variables are used to populate fields when features are added. However, as Matthias notes in the comments this doesn’t work on feature “update”. If this were possible then it would meet all our needs. Maybe something that could be funded? Ross *From:*Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] *Sent:* 22 August 2017 12:34 *To:* McDonaldR; qgis-user@lists.osgeo.org *Subject:* SV: [Qgis-user] Updating a field set with default global variable on edit Hi! I’ve made the same things with triggers in the database. To avoid getting same username I’ve chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. *Karl-Magnus * *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För *McDonaldR *Skickat:* den 22 augusti 2017 13:12 *Till:* qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> *Ämne:* [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "*created_by*" and "*updated_by*" fields. These are updated by a trigger in the database with the "*current_user*" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - *user_account_name* and *user_full_name* that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "*updated_by*" field to be updated with the current "*user_account_name*". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t: 01307 476419* This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons.Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. _
Re: [Qgis-user] Updating a field set with default global variable on edit
I love this mailing list. That is fantastic! We're testing this out right now and it seems to be working exactly as we need it. Thanks very much, Casper and Karl Magnus! Ross From: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] On Behalf Of Casper Bertelsen Sent: 22 August 2017 14:45 To: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] Updating a field set with default global variable on edit Hi Ross, If you make your project without saving username and password, the project will prompt for a user and password when opened. Then each member can login using their assigned login and thus making it possible to use current_user in postgres. To remove saved username and password from your current project file (make a copy, beforehand), you can open it in a text editor and seach for untill you find dbname='xx' host=yy port=5432 four lines above and remove the username and password-part only . Mvh. / Best regards Casper Bertelsen On 22/08/2017 15:36, McDonaldR wrote: Thanks Karl-Magnus We use triggers in the database already and they work perfectly but if we want to record the specific user creating and editing features then we'd have to create separate projects for each user. At the moment the team of about 10 users all use a set of centrally stored project files - one for each different task. I found this post on Stackexchange (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) where global variables are used to populate fields when features are added. However, as Matthias notes in the comments this doesn't work on feature "update". If this were possible then it would meet all our needs. Maybe something that could be funded? Ross From: Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] Sent: 22 August 2017 12:34 To: McDonaldR; qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Subject: SV: [Qgis-user] Updating a field set with default global variable on edit Hi! I've made the same things with triggers in the database. To avoid getting same username I've chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För McDonaldR Skickat: den 22 augusti 2017 13:12 Till: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Ämne: [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment. This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi All, to get field updates on vector layer edits (be they geometry or attribute edits) you can use the AutoFields plugin [1]. You can use expressions just like you do when setting default values in QGIS. Regards, Germán --- [1] http://geotux.tuxfamily.org/index.php/en/geo-blogs/item/333-autofields-plugin-for-qgis 2017-08-22 8:45 GMT-05:00 Casper Bertelsen : > Hi Ross, > > If you make your project without saving username and password, the project > will prompt for a user and password when opened. Then each member can login > using their assigned login and thus making it possible to use current_user > in postgres. > > To remove saved username and password from your current project file (make > a copy, beforehand), you can open it in a text editor and seach for > untill you find > > dbname='xx' host=yy port=5432 four lines above > and remove the username and password-part only . > > > Mvh. / Best regards > Casper Bertelsen > > On 22/08/2017 15:36, McDonaldR wrote: > > Thanks Karl-Magnus > > > > We use triggers in the database already and they work perfectly but if we > want to record the specific user creating and editing features then we’d > have to create separate projects for each user. At the moment the team of > about 10 users all use a set of centrally stored project files – one for > each different task. > > > > I found this post on Stackexchange (https://gis.stackexchange. > com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) > where global variables are used to populate fields when features are > added. However, as Matthias notes in the comments this doesn’t work on > feature “update”. If this were possible then it would meet all our > needs. Maybe something that could be funded? > > > > Ross > > > > *From:* Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se > ] > *Sent:* 22 August 2017 12:34 > *To:* McDonaldR; qgis-user@lists.osgeo.org > *Subject:* SV: [Qgis-user] Updating a field set with default global > variable on edit > > > > Hi! > > I’ve made the same things with triggers in the database. To avoid getting > same username I’ve chosen not to save the username and password in the > project. But then the user have to enter it every time the projects opens. > If you can find a solution to your suggested approach it would be nice. > > > > *Karl-Magnus * > > > > *Från:* Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org > ] *För *McDonaldR > *Skickat:* den 22 augusti 2017 13:12 > *Till:* qgis-user@lists.osgeo.org > *Ämne:* [Qgis-user] Updating a field set with default global variable on > edit > > > > Hi List, > > > > Anyone know how to update a field that has a global variable set as the > default value when records are edited (geometry and/or attributes)? > > > We have a QGIS project with a PostGIS layer loaded and configured with > look-ups and relations so the users can update all the relevant fields. > There are also "*created_by*" and "*updated_by*" fields. These are > updated by a trigger in the database with the "*current_user*" database > variable. This works fine if you are the current user that added the layer > to the project - the trigger works perfectly. > > If you give the project to someone else and they create and edit features > in the layer the fields are still updated with your name as you are the > person who added the layer to the project. Not what we want. > > QGIS has global variables and there are some new ones - > *user_account_name* and *user_full_name* that can be set as default > values for the fields. When a new feature is created the field is populated > with the correct name. Which is what we want. > > However, when we update an existing record we'd like the "*updated_by*" > field to be updated with the current "*user_account_name*". Like a > trigger in the database would do on update. Then we would have a better > idea of the history of the feature. > > > > Ross > > > > *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | > Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT* | t: > 01307 476419* > > > > > > This message is strictly confidential. If you have received this in error, > please inform the sender and remove it from your system. If received in > error you may not copy, print, forward or use it or any attachment in any > way. This message is not capable of creating a legal contract or a binding > representation and does not represent the views of Angus Council. Emails > may be monitored for security and network management reasons. Mes
Re: [Qgis-user] Updating a field set with default global variable on edit
Hi Casper, So nice to see you answering questions at the QGIS user list. Your knowledge is appreciated :) Lene Fischer Associate Professor University of Copenhagen Department of Geoscience and Natural Resource Management Forest and Landscape College Nødebovej 77a 3480 Fredensborg Denmark MOB +45 40115084 l...@ign.ku.dk<mailto:l...@ign.ku.dk> [cid:image001.gif@01D31B5F.195859E0] [cid:image002.png@01D31B5F.195859E0]<@Lene_Fischer> From: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] On Behalf Of Casper Bertelsen Sent: 22. august 2017 15:45 To: qgis-user@lists.osgeo.org Subject: Re: [Qgis-user] Updating a field set with default global variable on edit Hi Ross, If you make your project without saving username and password, the project will prompt for a user and password when opened. Then each member can login using their assigned login and thus making it possible to use current_user in postgres. To remove saved username and password from your current project file (make a copy, beforehand), you can open it in a text editor and seach for untill you find dbname='xx' host=yy port=5432 four lines above and remove the username and password-part only . Mvh. / Best regards Casper Bertelsen On 22/08/2017 15:36, McDonaldR wrote: Thanks Karl-Magnus We use triggers in the database already and they work perfectly but if we want to record the specific user creating and editing features then we'd have to create separate projects for each user. At the moment the team of about 10 users all use a set of centrally stored project files - one for each different task. I found this post on Stackexchange (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) where global variables are used to populate fields when features are added. However, as Matthias notes in the comments this doesn't work on feature "update". If this were possible then it would meet all our needs. Maybe something that could be funded? Ross From: Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] Sent: 22 August 2017 12:34 To: McDonaldR; qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Subject: SV: [Qgis-user] Updating a field set with default global variable on edit Hi! I've made the same things with triggers in the database. To avoid getting same username I've chosen not to save the username and password in the project. But then the user have to enter it every time the projects opens. If you can find a solution to your suggested approach it would be nice. Karl-Magnus Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För McDonaldR Skickat: den 22 augusti 2017 13:12 Till: qgis-user@lists.osgeo.org<mailto:qgis-user@lists.osgeo.org> Ämne: [Qgis-user] Updating a field set with default global variable on edit Hi List, Anyone know how to update a field that has a global variable set as the default value when records are edited (geometry and/or attributes)? We have a QGIS project with a PostGIS layer loaded and configured with look-ups and relations so the users can update all the relevant fields. There are also "created_by" and "updated_by" fields. These are updated by a trigger in the database with the "current_user" database variable. This works fine if you are the current user that added the layer to the project - the trigger works perfectly. If you give the project to someone else and they create and edit features in the layer the fields are still updated with your name as you are the person who added the layer to the project. Not what we want. QGIS has global variables and there are some new ones - user_account_name and user_full_name that can be set as default values for the fields. When a new feature is created the field is populated with the correct name. Which is what we want. However, when we update an existing record we'd like the "updated_by" field to be updated with the current "user_account_name". Like a trigger in the database would do on update. Then we would have a better idea of the history of the feature. Ross Ross McDonald | GIS Data Coordinator | Angus Council, People, IT | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419 This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this
Re: [Qgis-user] Updating a field set with default global variable on edit
On Tue, 22 Aug 2017 15:45:17 +0200, Casper Bertelsen wrote: > Hi Ross, > > If you make your project without saving username and password, the > project will prompt for a user and password when opened. Then each > member can login using their assigned login and thus making it > possible to use current_user in postgres. > > To remove saved username and password from your current project file > (make a copy, beforehand), you can open it in a text editor and seach > for untill you find > It seems to me that this can be useful for a lot of groups. Could this kind of save option be an interesting request for improvement? Slds. > dbname='xx' host=yy port=5432 four lines above > and remove the username and password-part only . > > > Mvh. / Best regards > Casper Bertelsen > > On 22/08/2017 15:36, McDonaldR wrote: > > > > Thanks Karl-Magnus > > > > We use triggers in the database already and they work perfectly but > > if we want to record the specific user creating and editing > > features then we’d have to create separate projects for each user. > > At the moment the team of about 10 users all use a set of centrally > > stored project files – one for each different task. > > > > I found this post on Stackexchange > > (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) > > > > where global variables are used to populate fields when features > > are added. However, as Matthias notes in the comments this doesn’t > > work on feature “update”. If this were possible then it would > > meet all our needs. Maybe something that could be funded? > > > > Ross > > > > *From:*Karl Magnus Jönsson > > [mailto:karl-magnus.jons...@kristianstad.se] *Sent:* 22 August 2017 > > 12:34 *To:* McDonaldR; qgis-user@lists.osgeo.org > > *Subject:* SV: [Qgis-user] Updating a field set with default global > > variable on edit > > > > Hi! > > > > I’ve made the same things with triggers in the database. To avoid > > getting same username I’ve chosen not to save the username and > > password in the project. But then the user have to enter it every > > time the projects opens. If you can find a solution to your > > suggested approach it would be nice. > > > > *Karl-Magnus * > > > > *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För > > *McDonaldR > > *Skickat:* den 22 augusti 2017 13:12 > > *Till:* qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> > > *Ämne:* [Qgis-user] Updating a field set with default global > > variable on edit > > > > Hi List, > > > > Anyone know how to update a field that has a global variable set as > > the default value when records are edited (geometry and/or > > attributes)? > > > > > > We have a QGIS project with a PostGIS layer loaded and configured > > with look-ups and relations so the users can update all the > > relevant fields. There are also "*created_by*" and "*updated_by*" > > fields. These are updated by a trigger in the database with the > > "*current_user*" database variable. This works fine if you are the > > current user that added the layer to the project - the trigger > > works perfectly. > > > > If you give the project to someone else and they create and edit > > features in the layer the fields are still updated with your name > > as you are the person who added the layer to the project. Not what > > we want. > > > > QGIS has global variables and there are some new ones - > > *user_account_name* and *user_full_name* that can be set as default > > values for the fields. When a new feature is created the field is > > populated with the correct name. Which is what we want. > > > > However, when we update an existing record we'd like the > > "*updated_by*" field to be updated with the current > > "*user_account_name*". Like a trigger in the database would do on > > update. Then we would have a better idea of the history of the > > feature. > > > > Ross > > > > *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT > > | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 > > | 1AT*| > > t: 01307 476419* > > > > This message is strictly confidential. If you have received this in > > error, please inform the sender and remove it from your system. If > > received in error you may not copy, print, forward or use it or any > > attachment in any
Re: [Qgis-user] Updating a field set with default global variable on edit
An option in the layer properties to save without username and password? Or a possibility to change database connection for a layer? To one connection with another user or a connection with no user stored. Could be useful. Though it is efficient to use an text editor to change it always feels a bit unsecure not using the GUI. Karl-Magnus Jönsson -Ursprungligt meddelande- Från: Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] För Fernando M. Roxo da Motta Skickat: den 23 augusti 2017 02:28 Till: qgis-user@lists.osgeo.org Ämne: Re: [Qgis-user] Updating a field set with default global variable on edit On Tue, 22 Aug 2017 15:45:17 +0200, Casper Bertelsen wrote: > Hi Ross, > > If you make your project without saving username and password, the > project will prompt for a user and password when opened. Then each > member can login using their assigned login and thus making it > possible to use current_user in postgres. > > To remove saved username and password from your current project file > (make a copy, beforehand), you can open it in a text editor and seach > for untill you find > It seems to me that this can be useful for a lot of groups. Could this kind of save option be an interesting request for improvement? Slds. > dbname='xx' host=yy port=5432 four lines above > and remove the username and password-part only . > > > Mvh. / Best regards > Casper Bertelsen > > On 22/08/2017 15:36, McDonaldR wrote: > > > > Thanks Karl-Magnus > > > > We use triggers in the database already and they work perfectly but > > if we want to record the specific user creating and editing features > > then we’d have to create separate projects for each user. > > At the moment the team of about 10 users all use a set of centrally > > stored project files – one for each different task. > > > > I found this post on Stackexchange > > (https://gis.stackexchange.com/questions/217011/how-to-automatically > > -update-wfs-edits-with-a-user-id) where global variables are used to > > populate fields when features are added. However, as Matthias notes > > in the comments this doesn’t work on feature “update”. If this > > were possible then it would meet all our needs. Maybe something > > that could be funded? > > > > Ross > > > > *From:*Karl Magnus Jönsson > > [mailto:karl-magnus.jons...@kristianstad.se] *Sent:* 22 August 2017 > > 12:34 *To:* McDonaldR; qgis-user@lists.osgeo.org > > *Subject:* SV: [Qgis-user] Updating a field set with default global > > variable on edit > > > > Hi! > > > > I’ve made the same things with triggers in the database. To avoid > > getting same username I’ve chosen not to save the username and > > password in the project. But then the user have to enter it every > > time the projects opens. If you can find a solution to your > > suggested approach it would be nice. > > > > *Karl-Magnus * > > > > *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För > > *McDonaldR > > *Skickat:* den 22 augusti 2017 13:12 > > *Till:* qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> > > *Ämne:* [Qgis-user] Updating a field set with default global > > variable on edit > > > > Hi List, > > > > Anyone know how to update a field that has a global variable set as > > the default value when records are edited (geometry and/or > > attributes)? > > > > > > We have a QGIS project with a PostGIS layer loaded and configured > > with look-ups and relations so the users can update all the relevant > > fields. There are also "*created_by*" and "*updated_by*" > > fields. These are updated by a trigger in the database with the > > "*current_user*" database variable. This works fine if you are the > > current user that added the layer to the project - the trigger works > > perfectly. > > > > If you give the project to someone else and they create and edit > > features in the layer the fields are still updated with your name as > > you are the person who added the layer to the project. Not what we > > want. > > > > QGIS has global variables and there are some new ones - > > *user_account_name* and *user_full_name* that can be set as default > > values for the fields. When a new feature is created the field is > > populated with the correct name. Which is what we want. > > > > However, when we update an existing record we'd like the > > "*updated_by*" field to be updated with the current > > "*user_account_n
Re: [Qgis-user] Updating a field set with default global variable on edit
On 8/22/17 3:36 PM, McDonaldR wrote: > Thanks Karl-Magnus > > > > We use triggers in the database already and they work perfectly but if > we want to record the specific user creating and editing features then > we’d have to create separate projects for each user. At the moment > the team of about 10 users all use a set of centrally stored project > files – one for each different task. > > > > I found this post on Stackexchange > (https://gis.stackexchange.com/questions/217011/how-to-automatically-update-wfs-edits-with-a-user-id) > where global variables are used to populate fields when features are > added. However, as Matthias notes in the comments this doesn’t work > on feature “update”. If this were possible then it would meet all > our needs. Maybe something that could be funded? > Great idea, I'd love to introduce an "also apply on update" checkbox to default values, I think that would extremely improve the possibilities of this feature (last modified timestamp with `now()`, last modified by with a username variable etc.) Matthias > > > > Ross > > > > *From:*Karl Magnus Jönsson [mailto:karl-magnus.jons...@kristianstad.se] > *Sent:* 22 August 2017 12:34 > *To:* McDonaldR; qgis-user@lists.osgeo.org > *Subject:* SV: [Qgis-user] Updating a field set with default global > variable on edit > > > > Hi! > > I’ve made the same things with triggers in the database. To avoid > getting same username I’ve chosen not to save the username and > password in the project. But then the user have to enter it every time > the projects opens. If you can find a solution to your suggested > approach it would be nice. > > > > *Karl-Magnus * > > > > *Från:*Qgis-user [mailto:qgis-user-boun...@lists.osgeo.org] *För > *McDonaldR > *Skickat:* den 22 augusti 2017 13:12 > *Till:* qgis-user@lists.osgeo.org <mailto:qgis-user@lists.osgeo.org> > *Ämne:* [Qgis-user] Updating a field set with default global variable > on edit > > > > Hi List, > > > > Anyone know how to update a field that has a global variable set as > the default value when records are edited (geometry and/or attributes)? > > > We have a QGIS project with a PostGIS layer loaded and configured with > look-ups and relations so the users can update all the relevant > fields. There are also "*created_by*" and "*updated_by*" fields. These > are updated by a trigger in the database with the "*current_user*" > database variable. This works fine if you are the current user that > added the layer to the project - the trigger works perfectly. > > If you give the project to someone else and they create and edit > features in the layer the fields are still updated with your name as > you are the person who added the layer to the project. Not what we want. > > QGIS has global variables and there are some new ones - > *user_account_name* and *user_full_name* that can be set as default > values for the fields. When a new feature is created the field is > populated with the correct name. Which is what we want. > > However, when we update an existing record we'd like the > "*updated_by*" field to be updated with the current > "*user_account_name*". Like a trigger in the database would do on > update. Then we would have a better idea of the history of the feature. > > > > Ross > > > > *Ross McDonald | *GIS Data Coordinator | Angus Council, People, IT | > Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| > t: 01307 476419* > > > > > > This message is strictly confidential. If you have received this in > error, please inform the sender and remove it from your system. If > received in error you may not copy, print, forward or use it or any > attachment in any way. This message is not capable of creating a legal > contract or a binding representation and does not represent the views > of Angus Council. Emails may be monitored for security and network > management reasons. Messages containing inappropriate content may be > intercepted. Angus Council does not accept any liability for any harm > that may be caused to the recipient system or data on it by this > message or any attachment. > > > > This message is strictly confidential. If you have received this in > error, please inform the sender and remove it from your system. If > received in error you may not copy, print, forward or use it or any > attachment in any way. This message is not capable of creating a legal > contract or a binding representation and does not represent the views > of Angus Council. Emails may be monitored for security and networ