Python GP tool to edit Postgres table

[EdwardParry1]

Problem: can't add new.rexord to related table in AGOL, so appreciate any feedback on if a Python script could work / if the workflow makes sense in general.

I am trying to create a spatially enabled document database, with a few more capabilities than the basic attachment manager. Collector is a possibility, but this is a school project, and I'm trying to get this to work as an enterprise gdb.

The first goal is to have a web app in which a user can add a document and enter attributes (author, type of document and the site it is related to).  A document can relate to one or many sites. I know it is not best DB design, but if a document relates to several sites, it is entered as a separate record each time for each site.

The second goal is to be able to SQL and spatial query- e.g. "which sites have documents of category x", "which sites have documents expiring in 30 days", "which documents are related to this group of sites"

The basic workflow in desktop is to start edit session, add a new record to tbl_doxuments, use attachment manager to upload a document to that record. When publishing as feature service, no built in widgets have the ability to add a new record to the related table (documents)

Data

1) FC_sites: point FC with names of sites. 1:m rc with tbl_doxuments 

2) Tbl_documents: postgres table with each documents attributes, and attachments have been enabled. The site name is also entered and that is the key to FC_sites

3) Tbl_documents_ATTACH: actual storage of each doc.

Proposed Python script

1) create a new record in tbl_documents - user enters document attributes. Retrieve the new object id

2) create a temporary match table for the one document to the one new record. (New OBJ ID+ filename)

3) call add attachments to add the file to the docunents table.

I would then publish this as a GP tool and set the parameters for the user to enter the various attributes/file path and upload the doc. I will probably then work with enhanced query widget to set up the different queries.

Notes

-Will using the Python gp tool actually be able create the new record in the Postgres DB?

- I do have a "BYTEA" field already in the tbl_documents, so possible to add a document directly to that table, but to use arcpy tools, think I have to have the separate documents_ATTACH table. 

I truly appreciate any feedback, and I will try to post code as I get it to work. Thanks

[Anonymous User · Accepted solution]

The Web AppBuilder Edit Widget does have the ability to add related records.

"To add related records (when available), scroll down the pop-up and click the pencil icon to the right of the related table.

A pop-up shows the records related to the attribute, if any. Click the + icon to open its pop-up and add the information."

Edit widget—Web AppBuilder for ArcGIS | ArcGIS 

[EdwardParry1]

Hi Katie-

Do you happen to have a live site with that functionality?  I have seen that in the Help, but it is not functioning on my web app.  I can click the +, but there is no action, also, it does not seem to allow it on a site that does not currently ave any related records. .

Possible factors issues that I am working through:

1) Would it be a factor that it is a Postgres table with a relationship class as opposed to a relate?

2) the table is non-versioned, but the FC is versioned.  Tried to set the table as versioned, but showed my one of my postgres schemas having a lock

3) maybe there is a lock from desktop or pgAdmin that is not allowing it to commit

I set up a python GP tool (using insert cursor), again, works fine on Desktop and publishes, but shows a "Submitting...Executing...Failure" message in weppApp builder

Link to the app.  GP # 1 allows user to specify table (in desktop tool GUI), the GP #2 is hardcoded to the table

http://ncsu.maps.arcgis.com/apps/webappbuilder/index.html?id=cd5ec81eb9f34a0c9a982fbc8422b872

[KyleBlessman]

Katie, I have seen the edit widget and have tried to use it for a similar goal but it does not seem possible to upload attachments (.docx, .pdf, .xlsx...) into the related table due to the underlying process. The processes that I am referring to is that when generating a BLOB field that houses attachments a related table is created to host the document contents and metadata. Therefore when relating the first table to the feature class there is also a secondary related table attached. This processes is supported by the desktop version and when using the pre-designed theme crowdsource polling but crowdsource polling does not allow for much customization.