Latest Contributions by Bud

Re: Add join/join field with expression · ‎12-14-2022

Related: User-defined SQL join clause

‎12-14-2022

https://www.screentogif.com/ https://community.esri.com/t5/community-feedback/what-screen-recording-app-do-you-use/m-p/1240223#M1520

‎12-12-2022

Credit goes to @jcarlson for pointing out Beekeeper Studio and VSCode in this post: What SQL client do you recommend for querying mobile geodatabases?

‎12-12-2022

Thanks for the info. I was able to open my mobile geodatabase using your instructions. But haven't been able to run a select query on one of it's tables yet. Now that I'm looking at VSCode more closely, I don't think I understand why we'd want to use it for querying a database, such as a mobile geodatabase. It seems to me that VSCode wouldn't be a very good SQL client. That's not what it's designed for. Why not use a proper SQL client, like Beekeeper Studio, or something similar? I'm likely just missing something. I haven't used VSCode much enough to get good at it. I find it rather frustrating to use at this point.

‎12-12-2022

ArcGIS Pro 3.0.3; mobile geodatabase. I have a definition query on a standalone table: OBJECTID IN (3, 5, 11) In a FC, I create a join to the table with the definition query (join: ASSET_ID=ASSET_ID). The definition query gets applied to the joined FC, and the OBJECTID gets automatically prefixed with the table name: main.RoadInspTable.OBJECTID IN (3, 5, 11) That works as expected. Next, I do the same thing, but this time using a more complex definition query, one that has a subquery: objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) Use case for the complex definition query: https://community.esri.com/t5/arcgis-pro-ideas/one-to-first-joins-control-what-related-record-is/idc-p/1239993/highlight/true#M22488 When I go to create the join, I use the Validate Join tool first. The join is valid. But when I create the join, it fails: Failed to refresh table. Error: Underlying DBMS error [ambiguous column name: objectid] [Roads][STATE_ID = 0] The problem is: ArcGIS Pro hasn't given OBJECTID a prefix in the joined FC's definition query. How it is: objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) How it should be: roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) If I manually add the prefix to the joined FC's definition query, then it works as expected. The error goes away. But it took some time to figure out why that was happening. Since prefixes already get added to columns in simple definition queries, could the same thing happen for more complex definition queries? (ones with subqueries like above) That would avoid some headache. Thanks.

‎12-12-2022

It looks like the answer is yes. Steps: Copy the data from Excel. Create a new row by hitting "Click to add new row" at the bottom of the table. Hit enter to commit the row. Hit the ESCAPE keyboard key to stop editing that cell. Use CTRL+V to paste the data. New rows have been created from the pasted data, as expected. GIF:

‎12-12-2022

Background: I have an table in ArcGIS Pro that has existing rows. I'm able to copy/paste rows from an Excel spreadsheet to replace the existing Attribute Table data. Like this: Click on an existing cell in the Attribute Table Use CTRL+V to paste GIF: That works as expected. The existing data has been replaced. Question: I want to do something similar to the above, but this time I want to paste Excel data as new rows in the attribute table, not replace the data in existing rows. Is there a way to paste data from Excel as new rows? ArcGIS Pro 3.0.3; mobile geodatabase. Related: Pasting rows from Excel into Attribute Table using CTRL+V works; do it with the paste button too

‎12-12-2022

I have a table in ArcGIS Pro that has existing rows. I'm able to copy/paste rows from an Excel spreadsheet into the Attribute Table like this: Click on a cell in the Attribute Table Use CTRL+V to paste GIF: That works as expected. However, while I can use CTRL+V in that scenario, the Paste button in the ribbon is greyed out. It would make more sense if the Paste button were enabled. So that we could use that button if preferred over the keyboard shortcut. But also to communicate to the user that pasting is possible in that scenario. Users might not be aware of that functionality. Could that behavior be changed? Thanks. ArcGIS Pro 3.0.3; mobile geodatabase. Related: Copy/paste data from Excel into attribute table as new rows

‎12-12-2022

Like this:

‎12-12-2022

Here's the data and SQL expression -- in case someone wants to reproduce that UI behavior: Standalone table named roadinsptable: ASSET_ID: Text DATE_: Date CONDITION: Long ASSET_ID DATE_ CONDITION 1 2016-04-01 1 1 2019-03-01 2 1 2022-01-01 3 2 2016-04-01 4 2 2022-01-01 5 3 2022-01-01 6 3 2022-03-01 7 3 2018-03-01 8 3 2013-03-01 9 3 2010-06-01 10 3 2022-03-01 11 3 2022-03-01 11 SQL Expression: roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) ArcGIS Pro 3.0.3; mobile geodatabase.

‎12-12-2022

What screen recording tool do you use for putting GIFs in Esri Community posts? I’ve been using ScreenToGIF (https://www.screentogif.com) recently, suggested to me by @wayfaringrob. It seems to work well. The key to a small file size seems to be to avoid moving windows around; only make small movements like moving/clicking the mouse. The attachment file size limit is just under 10 mb. Example: I think that file was under 1 mb in size. But if I moved windows around, it’d probably be over the 10 mb limit. Screen recordings are way easier than making a bunch of screenshots and typing out descriptions. We should all probably use them more.

‎12-11-2022

Could Esri provide documentation on what SQLite version is bundled with each ArcGIS Pro release? I’m aware that I can check the SQLite version on a computer that has ArcGIS Pro installed by using the ArcGIS Pro Python Command Prompt. But it would be helpful to have that info in the docs so we can see what version of SQLite is bundled with other versions of ArcGIS Pro too. Thanks.

‎12-11-2022

It looks like the SQLite version that is bundled with ArcGIS Pro 3.0.3 is SQLite 3.38.2 as well. Thanks.

‎12-11-2022

I'm in the process of creating a join from a FC to a table that has a definition query (the table has 12 rows, but the definition query pares it down to 3 rows). When I hit the Validate Join button, I get these results in the Message window: Checking for join cardinality (1:1 or 1:m joins)... A one - to - many join has created 12 records from 3 matches. The input table has 3 and the join table has 3 records. I don't think that information is correct. The join is 1:1 and it will result in 3 records in the source FC (same as before the join). But the Validate Join message says differently. Is that a known issue? Thanks. ArcGIS Pro 3.0.3; mobile geodatabase.

‎12-11-2022

Here's a workaround for enterprise geodatabases and mobile geodatabases (not file geodatabases). It also works for Excel files since ArcGIS uses SQLite SQL for Excel file SQL expressions. Create a definition query on the join table -- to pare down the rows so that there's only one row for each ID. Examples: Oracle (FETCH): roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC FETCH FIRST ROW ONLY ) SQLite/mobile geodatabase and Excel files (LIMIT): roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) (likely works for other databases too) Those queries only return one row per ASSET_ID (the field I'll be joining on). If there is a tie, then it uses a different field, CONDITION, to break the tie. Additional tie-breaker fields can be easily added to the ORDER BY clause. For other use cases, LIMIT 1 (or FETCH) could be changed so that it returns multiple rows per ASSET_ID, not just one row, if required. More info here: Using a WHERE clause subquery, select the greatest n per group with a tie-breaker Next, join to the table that has the definition query. Result: That lets us control what related record is used in the join. The join is now 1:1 instead of one-to-first or 1:M. So that satisfies the original problem, but only for datatypes that have full SQL support. So it doesn't help us for file geodatabases or other datatypes that don't support correlated subqueries in SQL expressions. More info: Support correlated subqueries in file geodatabase SQL expressions. Edit: On closer inspection, there's a problem with this approach (ArcGIS Pro 3.0.3; mobile geodatabase). When I try to create the join on the table with the definition query, I get an error: Failed to refresh table. Error: Underlying DBMS error [ambiguous column name: objectid] [Roads][STATE_ID = 0] I tried adding prefixes to all fields in the subquery, but that had no effect on the error: objectid IN ( SELECT r2.objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY r2.date_ DESC, r2.condition DESC LIMIT 1 ) I'm not sure what the cause is. I have the same problem in an Oracle 18c 10.7.1 enterprise gdb (ArcGIS Pro 2.6.8): Failed to refresh table. Error: Underlying DBMS error [ORA-00918: column ambiguously defined] [DBJoin1] Solved: I had to add a table prefix (aka "fully qualify the table name") for the objectid in the first line: Before: objectid IN ( After: roadinsptable.objectid IN ( I've updated the original queries. Idea: Join to table with definition query — column name should get prefixed (SQL expression with subquery) BUG-000155819: Attribute table of joined layer in Mobile Geodatabase does not open when a definition query with a subquery is applied to the join table.

My Ideas

Latest Contributions by Bud

Re: Add join/join field with expression

Re: Copy/paste data from Excel into attribute table as new rows

Re: Open mobile geodatabase using VSCode

Re: Open mobile geodatabase using VSCode

Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery)

Re: Copy/paste data from Excel into attribute table as new rows

Copy/paste data from Excel into attribute table as new rows

Pasting rows from Excel into Attribute Table using CTRL+V works; do it with the paste button too

Re: Drag table from Contents pane to map or Attribute Table to open

Re: Make definition query's toggle more intuitive

What screen recording app do you use?

Documentation on SQLite version bundled with ArcGIS Pro releases

Re: Mobile geodatabase created by ArcGIS Pro 3.0.3 — What SQLite version is the .geodatabase file?

Validate Join message has incorrect information (join to table with definition query)

Re: One-to-first joins: Control what related record is used

Integrate ArcGIS Data Pipelines in ArcGIS Enterpri...

Send configurable email notifications from ArcGIS ...

Re: How reliable are ArcGIS Online webhooks?

How reliable are ArcGIS Online webhooks?

Sharepoint List Connector

GIS Jobs

Roads and Highways User Group (RHUG)

Local Government Solutions Users

Mapping and Charting Enterprise Databases

ArcGIS Arcade