"Rapid Response" column in Roadway Markings Work Orders dataset incorrectly named and populated with blank values

[atdservicebot]

What application are you using?

Other / Not sure

Describe the problem.

Socrata Open Data Portal - Rapid Response column in the roadway markings work orders dataset now show blanks instead of TRUE/FALSE. It might have been because of the field name change for Rapid Response Maintenance Yes/No to Safety Device Maintenance Yes/No.

I would like to have the column name changed to Safety Device Maintenance and to have the column populated with TRUE/FALSE as reflected in data tracker.

Website Address

https://data.austintexas.gov/Transportation-and-Mobility/Roadway-Markings-Work-Orders/nyhn-669r

Internet Browser: Firefox

Requested By Susanne G.

Request ID: DTS23-106095

[patrickm02L]

Rewrote the User Story from the description above.

As a user of the Socrata Open Data Portal, I want the "Rapid Response" column in the "roadway markings work orders" dataset to be renamed as "Safety Device Maintenance" and have the values populated with "TRUE" or "FALSE" so that I can easily understand the data and use it for my analysis. This change will allow me to accurately interpret the data and make informed decisions based on it.

[ChristinaTremel]

From SMD sprint review, if it can be fixed in the next few weeks or so this is a high priority.

Needs to be coordinated with Prod and Dev. Can batch it with the AMD request.

[amenity]

@dianamartin @ChristinaTremel - we talked about this "type" of issue in Prod+Dev sync. From a dev perspective, it would be helpful to have an issue template for these so that they include a checklist of the kinda weird steps the devs need to perform. Is there any specific info that would be useful for the apps team?

@johnclary when you get time - could you fill out the Dev steps for these types of issues?

Looking at this issue and #11322 drafting a template here (please edit directly):

---

[Open Data Schema Update]

General

• Requestor & explanation
• Socrata URL
• Knack API view URL

Specs

Old Name	New Name	Note

Steps

ex. If the request is Open Data Portal (ODP), remove the steps for updating ArcGIS Online (AGOL) and vice versa..

If Open Data ETL from Knack

• Apps + ODP (Apps) + Dev

Apps

• [ ] Locate the API View
• Locate the API view in the Knack application that serves as the source for the ArcGIS dataset. Each of our Knack applications will have a series of views (aka, pages) which we call "API Views" and are purpose-built for publishing data from Knack to external sources (Socrata, ArcGIS Online, etc.).
• To locate the API view, review this and locate the view ID for the dataset in question. This view ID will look something like this: view_101. You can use the to search by this view ID and bring up the editing page in Knack builder.
• [ ] Field Naming

The field names in Knack need to exactly match, except for capitalization, the field names in AGOL and Socrata In Knack parlance, field names are called "Labels".

• There are very few restrictions in Knack around how you can name a field. It can have spaces, numbers, mixed case, etc. This is because Knack maintains it's own unique field identifiers which you as a builder cannot modify, e.g. field_101, etc.
• Crucially, we use the user-defined Knack field label as the field name in the downstream system. In Socrata, these field names are called the "API Field Names", and they must be lower case, with no spaces or special characters.
• In ArcGIS Online, these fields may be upper or lower case, with no spaces or special characters.
• As such, the field "label" of any Knack field which is to be published to an external system should follow these same conventions.
• [ ] Update the Knack View
• Once you've located the API view in the Knack builder, first verify (1) that the fields are named appropriately (as described above) and (2) if the columns you want to include in the destination dataset are already present in the view.
• If they fields are not present, add them. Otherwise, continue to the next step.

ODP (Apps)

Updating Open Data Portal GitBook reference

• [ ] Update Open Data Portal Data set
• Login to Enterprise Data Portal and navigate to the Primer page of the dataset you wish to modify.
• Scroll down to the Columns in this Dataset section and verify that the fields you wish to add are not present in the dataset.
• To add new fields, click on the blue Editbutton in the top right corner of the Primer page.
• Click on the button below Review & Configure Data.
• From the configuration editor, click the Add Column button. Fill out the form fields as follows:
  • Display Name: A human-friendly field label
  • Field name: this is the "API Field Name" which should exactly match the Knack field label (with the exception that value should be lower case. See note in step #2.)
  • Description: a detailed description of what values the field contains, the purpose it serves, etc.
  • Source Column: Select "No Source column"
  • Type: choose the appropriate field type.
• Click Save to save your changes. Return to the Primer page, and click the blue Update button in the top-right of the page to publish the dataset changes.

Dev

• [ ] In order to refresh the Open Data Portal dataset with values for the new columns, you should run a batch update in the Knack builder to set the modified date of all records to the current date.
• This will enable the integration script to update the destination datasets the next time it runs.
• [ ] Please read Dev Schema Change Docs
• [ ] Knack.py page

If ArcGIS Online ETL from Knack

• Apps + AGOL (Geo) + Dev

Apps

• [ ] 1. Locate the API View
• Locate the API view in the Knack application that serves as the source for the ArcGIS dataset. Each of our Knack applications will have a series of views (aka, pages) which we call "API Views" and are purpose-built for publishing data from Knack to external sources (Socrata, ArcGIS Online, etc.).
• To locate the API view, review this and locate the view ID for the dataset in question. This view ID will look something like this: view_101. You can use the to search by this view ID and bring up the editing page in Knack builder.
• [ ] 2. Field Naming

The field names in Knack need to exactly match, except for capitalization, the field names in AGOL and Socrata In Knack parlance, field names are called "Labels".

• There are very few restrictions in Knack around how you can name a field. It can have spaces, numbers, mixed case, etc. This is because Knack maintains it's own unique field identifiers which you as a builder cannot modify, e.g. field_101, etc.
• Crucially, we use the user-defined Knack field label as the field name in the downstream system. In Socrata, these field names are called the "API Field Names", and they must be lower case, with no spaces or special characters.
• In ArcGIS Online, these fields may be upper or lower case, with no spaces or special characters.
• As such, the field "label" of any Knack field which is to be published to an external system should follow these same conventions.
• [ ] 3. Update the Knack View
• Once you've located the API view in the Knack builder, first verify (1) that the fields are named appropriately (as described above) and (2) if the columns you want to include in the destination dataset are already present in the view.
• If they fields are not present, add them. Otherwise, continue to the next step.

AGOL (Geo)

Updating ArcGIS Online feature layer GitBook reference

• [ ] Update ArcGIS Online data set
• Login to ArcGIS Online and navigate to the main page for the feature layer you wish to modify.
• Click on Data along the top of the page, then Fields at the top right. Check the existing fields to ensure the field(s) you wish to add are not already present in the feature layer.
• To add new fields, click on the + Add button in the top left corner of the page.
• The Add Field window will open. Fill out the form as follows:
  • Field Name: this is the "API Field Name which should exactly match the Knack field label
  • Display name: can be the same as the Field Name or a human friendly field label, depending on the use of the feature layer
  • Type: choose the appropriate field type. For text fields, be sure to set the right character limit. You should err on the side of more characters.
  • Length: choose the appropriate field length if the Type is a String
  • Default Value: this is an optional field to fill out
• Click Add New Field to save your changes. The new field will appear at the bottom of the field list.

Dev

• [ ] Ask a dev to manually run both the metadata_to_postgrest and records_postgrest scripts for this dataset, and let them know that the data needs to be fully refreshed.
• The dev should run the script by omitting the --date flag in run command.
• [ ] Please read Dev Schema Change Docs
• [ ] Knack.py page

cc/ @Charlie-Henry @chiaberry @johnclary @mddilley

[ChristinaTremel]

@amenity - @dianamartin and I worked on updating the steps above ⬆️ . We included two scenarios for these ETL type requests:

• If Open Data ETL from Knack
• If ArcGIS Online ETL from Knack

We included the steps that would be useful for the apps team and the geo team with these requests!

[ChristinaTremel]

@KaroEngstrom will update the ODP field in advance to match! Will schedule time with Dev to update the script.

[KaroEngstrom]

W 3/8/23

• updated the column display name and field name on City Data Hub/EDP and published to ODP

[chiaberry]

@KaroEngstrom & @ChristinaTremel , I ran the steps to update the metadata in postgrest and then rerun all the records to socrata, but the column was still showing up blank.

Then I checked the view in knack, and that column is blank there too, which means theres nothing for socrata to get from that column. Should the column be currently blank in knack?

[ChristinaTremel]

@chiaberry Many of the rows will be blank because this field was added July 2022 and any work order created before that time won't have data in this field. I'm actually looking at the ODP dataset and I'm seeing ~970 records with information and it looks to be as recent as records created 3/8/2023. So I think it's working! 🙂

[chiaberry]

Yay! My dev side is done then, can we close the issue?

[ChristinaTremel]

I think we can! Let me update Signs & Markings that this has been completed - I'll update the Github issue with what I send and close it out.

[ChristinaTremel]

Sent Susanne message: Hey there! The "Safety Device Maintenance" field on the Markings Work Orders in the open data portal is now working!!  It's been updated to reflect the new field name and I see data in that field as recent as 3/8. Link to the Dataset.