Filter a Report by Project Name Using a Chained Query

// 1. Set the project name to filter on.
//    This could also be driven by a $params value for a dynamic report.
const projectName = 'Project Alpha';  // Replace with your project name

// Escape single quotes so the name can't break out of the SQL string
const safeProjectName = projectName.replace(/'/g, "''");

// 2. Look up the project_id from the Fulcrum system projects table.
const projectInfo = QUERY(`
  SELECT project_id
  FROM projects
  WHERE name = '${safeProjectName}'
`);

const projectId = projectInfo?.rows?.[0]?.project_id;
if (!projectId) {
  throw new Error('Project not found');
}

// 3. Use the resolved project_id to filter the main data query.
//    Replace "Your App Name" with your app's exact name.
const records = QUERY(`
  SELECT *
  FROM "Your App Name"
  WHERE _project_id = '${projectId}'
  ORDER BY _updated_at DESC
`);

// Caller passes ?project_name=Project+Alpha in the report URL
const projectName = $params.query?.project_name || $params.post?.project_name || '';

// $params values are external input. Validate against an allow-list when you
// can; otherwise escape single quotes before using the value in SQL.
const allowedProjects = ['Project Alpha', 'Project Beta'];  // Replace with your project names
if (!allowedProjects.includes(projectName)) {
  throw new Error('Unknown project');
}
const safeProjectName = projectName.replace(/'/g, "''");

const projectInfo = QUERY(`
  SELECT project_id FROM projects WHERE name = '${safeProjectName}'
`);

const projectId = projectInfo?.rows?.[0]?.project_id;

// Stop if no project matched, rather than querying with an undefined ID
if (!projectId) {
  throw new Error('Project not found');
}

const records = QUERY(`
  SELECT * FROM "Your App Name"
  WHERE _project_id = '${projectId}'
  ORDER BY _updated_at DESC
`);

const records = QUERY(`
  SELECT *
  FROM "Your App Name"
  WHERE _project_id = '${projectId}'
    AND _updated_at >= NOW() - INTERVAL '7 days'
  ORDER BY _updated_at DESC
`);