List of common use cases to document

[nickdickinson]

What are common use cases that we could document?

• From customer success team
• From existing batch scripts commonly used
• Existing tutorial that may need updates

Let's use this issue to track these for the next release.

[nickdickinson]

Use cases from existing issues and features. Shall we include this in the work?:

• Getting records from nested forms / references etc. (from milestone 4.37 issues)
• Updating / importing reference fields importRecords does not match on form label/key field label when importing references #129 R Package - Importing Data to a Reference Field #92 - really an example of creating a new form programmatically with sub-forms and reference fields and loading data could do this.
• Moving forms and creating folders Add / change folders #121
• Creating roles for a team Grant-based role end-to-end tutorial #133
• Get metadata about the content of roles, forms, and databases in a tidy tibble/table format expose more meta-info for forms/databases (like # of total records in a form/db) #39
• Get billing and database metadata API for fetching metadata for database and billing accounts #26
• List all users and roles and resources API for fetching metadata for database and billing accounts #26
• Adding / updating attachments Stage and upload attachments #135

[nickdickinson]

@jamiewhths In our meeting, let's talk about how to link up with the customer success team and see if you can give me access to any commonly used scripts. The prior comment is mainly to help organize related issues that we may be able to close at the same time.

[jamiewhths]

@nickdickinson sent you a file via email from CS which covers some of the most common use cases in an early form of documentation which could be expanded out.

[jamiewhths]

Order of priority:

• Creating roles for a team Grant-based role end-to-end tutorial #133
• Processing examples from CS into tutorials/how-tos
• Adding / updating attachments Stage and upload attachments #135

---

• Getting records from nested forms / references etc. (from milestone 4.37 issues)
• Updating / importing reference fields importRecords does not match on form label/key field label when importing references #129 R Package - Importing Data to a Reference Field #92 - really an example of creating a new form programmatically with sub-forms and reference fields and loading data could do this.
• Moving forms and creating folders Add / change folders #121
• Get metadata about the content of roles, forms, and databases in a tidy tibble/table format expose more meta-info for forms/databases (like # of total records in a form/db) #39
• Get billing and database metadata API for fetching metadata for database and billing accounts #26
• List all users and roles and resources API for fetching metadata for database and billing accounts #26

[Ryo-N7]

a few more examples:

• get database roles function:

getDatabaseRoles <- function(databaseId) {
  # vimplusdbid <- "c9r36oole40p8ex2j30"
  databaseTree <- getDatabaseTree(databaseId)

  dbrolesdf <- dplyr::tibble(
    id = unlist(lapply(databaseTree$roles, function(x) {x$id})),
    label = unlist(lapply(databaseTree$roles, function(x) {x$label})),
    permissions = lapply(databaseTree$roles, function(x) {x$permissions}),
    parameters = lapply(databaseTree$roles, function(x) {x$parameters}),
    filters = lapply(databaseTree$roles, function(x) {x$filters}),
    grants = lapply(databaseTree$roles, function(x) {x$grants}),
    version = unlist(lapply(databaseTree$roles, function(x) {x$version})),
    grantBased = unlist(lapply(databaseTree$roles, function(x) {x$grantBased}))
  )

  return(dbrolesdf)
}

• search for resource across ALL databases on account based on resource ID (can modify to use resource label, etc...):

getdbrecs_df <- function(dbid, dblabel) {
  dbrecdf <- activityinfo::getDatabaseResources(dbid) %>%
    dplyr::mutate(
      databaseId = dbid,
      dblabel = dblabel) %>%
    dplyr::rename(resourceId = id, resourceLabel = label)

  return(dbrecdf)
}

search_resource_activityinfo <- function(resourceId) {

  alldbsdf <- activityinfo::getDatabases() %>% dplyr::select(databaseId, label)
  alldb_ids <- alldbsdf$databaseId
  alldb_labels <- alldbsdf$label

  alldbrecs <- purrr::map2(alldb_ids, alldb_labels, ~ getdbrecs_df(.x, .y))

  alldbrecs_df <- purrr::reduce(alldbrecs, bind_rows)

  if (is.na(resourceId)) {
    return(alldbrecs_df)
  } else {
    dbrec_filtered <- alldbrecs_df %>% dplyr::filter(resourceId == resourceId)
    return(dbrec_filtered)
  }
}

• get all 'Inactive' roles from databases

alldbs <- getDatabases()

get_all_inactive_ids <- function(databaseId) {
  ## search db schema for role info
  dbschm <- getDatabaseTree(databaseId)
  
  dbroles <- dbschm[["roles"]]
  
  ## the ID for "Inactive" role
  inactiveid <- dbroles %>% 
    keep(~ .x$label == "Inactive") %>% 
    map_chr(~ .x$id)
  
  inactdf <- data.frame("db_id" = databaseId,
                        "inactive_id" = inactiveid)
  
  return(inactdf)
}

safe_get_all_inactive_ids <- purrr::safely(get_all_inactive_ids)

allinactids_output <- map(alldbsclean$databaseId, 
                      ~ safe_get_all_inactive_ids(databaseId = .x))

allinactids_df <- allinactids_output %>% 
  map("result") %>% 
  discard(is.null) %>% 
  reduce(bind_rows)

• find all forms in database with 'public' visibility setting:

PrList <- getDatabases()

get_public_visibles <- function(dblabel, dbid) {
  cat(paste0("\n\nDatabase: ", dblabel, "\n\n"))
  dbrecs <- getDatabaseResources(dbid)
  dbrecs <- dbrecs %>% 
    filter(visibility == "PUBLIC", type == "FORM") %>% 
    mutate(project = dblabel,
           url = AVDGconvenience::getActivityInfoURL(formId = id)) %>% 
    select(project, id, label, visibility, url)
  cat(paste0("\n\nDatabase: ", dblabel, "... Done!\n\n"))
  return(dbrecs)
}

allpubvisdf <- map2_dfr(PrList$label, PrList$databaseId, ~ get_public_visibles(dblabel = .x, dbid = .y))

• database backup script

# Generalized ActivityInfo database back-up script
# Run at the end of every month (or whatever other time interval)

# Packages ----
# Install the following packages:

# install.packages("devtools")
# devtools::install_github("bedatadriven/activityinfo-R")
# install.packages("here")

library(activityinfo)
library(here)

# Generate ActivityInfo API token ----
# Please see the following instructions below to generate your API token so you can interact with ActivityInfo through R:
#   
# 1. Click on your account name in the top right corner bar and click on "Account settings".
# 2. Click on "API Tokens" in the left side bar.
# 3. Then click "Add".
# 4. Set a label for your API token then choose "Read & Write" button in "Scope", finally click "Generate" to create the API token.
# 5. You'll be given a chance to copy-paste your full API token somewhere. Do so in a secure location as you won't have another opportunity to copy the full token after this time.
# 6. Now in your R session you can use the `activityInfoToken()` function to set everything up. Note, in general, __you should never include your API token in an R source file or check such sources with tokens into version control tools like git__.

# Set up file/directory paths ----
# Folders are pre-created in the back-up directory according to the date
# the `here()` function sets up the file-path to the current project root
path <- here("activityinfo_database_backup")

#  If directory/folder doesn't exist yet, create it.
if (!dir.exists(path)) {
  dir.create(path)
}

## Monthly back-ups:
yr <- format(Sys.Date(), "%Y")
yrmonth <- format(Sys.Date(), "%Y_%m")
fileLoc <- sprintf("%s/%s/%s", path, yr, yrmonth)

## Weekly back-ups
yr <- format(Sys.Date(), "%Y")
yrmonth <- format(Sys.Date(), "%Y_%m")
yrweek <- as.numeric(format(Sys.Date(), "%U"))  ## NOTE: week of the YEAR (not the month)
fileLoc <- sprintf("%s/%s/%s/%s", path, yr, yrmonth, yrweek)

#  If directory/folder doesn't exist yet, create it.
if (!dir.exists(fileLoc)) {
  dir.create(fileLoc, recursive = TRUE)
}

# Get all existing forms in database ----
# Resources are the forms and folders in ActivityInfo
database_id <- "c5h9mudkz3sqce52" 
formlist <- getDatabaseResources(database_id)

# We only want resources that are forms and subforms 
formListClean <- subset(formlist, type %in% c("FORM", "SUB_FORM")) 

# Grab data and save ----
# The queryTable function gets ALL the columns, including those of the referenced forms which can be extraneous
# So first use getFormSchema() to get the individual fields and use this to limit the fields obtained from queryTable()
# We will only get the fieldId and fieldLabel because we already obtained the formId, parentId, and formLabel earlier

for (n in 1:nrow(formListClean)) {
  message(sprintf("Form %s of %s", n, nrow(formListClean)))
  schema <- getFormSchema(as.character(formListClean[n, "id"]))
  ## erase `/`, square brackets, parentheses, etc. from labels as that would mess up the file names
  schema$label <- gsub(x = schema$label, pattern = "\\/", replacement = "-")
  schema$label <- gsub(x = schema$label, pattern = "\\[|\\]", replacement = "-")
  schema$label <- gsub(x = schema$label, pattern = "\\(|\\)", replacement = "-")
  
  if (length(schema$elements) == 0) { next } # Do not proceed if form has no fields
  # queryTable() function looks like this:
  # queryTable(formid,
  #    "fieldLabel" = "fieldId",
  #    "fieldLabel" = "fieldId")
  # So we need to store these individual columns as a string to pass it to queryTable()
  schemaElements <- c("_id")
  schemaHeaders <- c("ID")
  
  for (index in 1:length(schema$elements)) {
    id <- schema$elements[[index]]$id
    label <- schema$elements[[index]]$label
    schemaElements <- append(schemaElements, id)
    schemaHeaders <- append(schemaHeaders, label)
    toString(schemaElements)
    toString(schemaHeaders)
    cols <- c(setNames(schemaElements, schemaHeaders))
  }
  # Run queryTable() to grab the data
  data <- queryTable(schema$id, cols)
  # Write data to its designated folder
  message(sprintf("Writing data for %s\n", schema$label))
  write.csv(data, file = sprintf("%s/%s_%s.csv", fileLoc, schema$label, Sys.Date()))
}

message("End of script.")