Hi there!
Sorry if some of this is a little oddly put; I don't have much experience with ArcGIS (any of its services). I am hoping some elegant solution exists for what I want to do and I just have not found it yet. Feel free to ask for more information.
Basically I have 2 tables currently: a "verification" table that I want to have a FK to a Survey 123 form's globalid. Currently this is sort of setup using Join Features between the table and form data.
What I want to do is essentially add a new record to the "verification" table when a new form is submitted, that defaults to False/0 (it has not been verified). (Also, the user who submits the new form doesn't necessarily have access with Auth to be able to access the verification table layer programmatically). If there's an easy way to create that default record in the "verifications" table automatically, please let me know!
My current idea for it is performing just like a LEFT JOIN on the form data table and the verification table and then filter by where the FK is NULL so I know which records are in the form data table and not in the verification table. From there, I can just add the new records' globalids into the verification table. I want to be able to do this all programmatically with the ArcGIS REST JS API, which I know has a queryFeatures and queryRelated but I can't figure out how to relate the two tables or perform a SQL query with that API that allows multiple tables to be referenced. Being able to just run an arbitrary SQL query would solve this or how to LEFT JOIN with that API or setup a LEFT JOIN relationship in ArcGIS Online would also solve it (I think). I've found stuff on ArcGIS Insights to do the LEFT JOIN type of thing but I have no idea how insights works or if it's compatible with that REST API (guessing not).
This has led me to consider (although I haven't quite yet) querying for all of the FK globalids in the verification table and then programmatically constructing a long WHERE clause to pass into queryFeatures. This just feels wrong though and like there is probably a better way, since this would cause dips in performance as the size of the datasets went up.
I've been trying to avoid using a backend for this that gets updates when a form is submitted to lower the cost of the project, since I feel like all of this should be doable using the JS REST API by just a properly authenticated user within ArcGIS.
Any help with how best to do this is much appreciated and sorry again if the descriptions are a bit wonky! I can give more information wherever needed. Thanks!
