Merging two sheets in a report prior to API pull?

CRana

We run an intake process within a main sheet where people submit ideas (say there are 5 statuses). When it reaches a particular status (say status 2), the main sheet pushes that row to another sheet where we track a sub process. When that subprocess is complete, we manually go back to the main sheet and switch the status to the next one.

We're trying to make this as transparent to users who have submitted ideas and using the API to put it on a dedicated microsite, so I'm trying to optimize this as much as possible. The main issue I'm running into is that subprocess complicating things, as nothing pushes back from there, so I don't have a single source of truth, as that subprocess is being displayed as well, for example:

1. Status 1 - complete
2. Status 2 - in progress
   1. Process 1 (checked off)
   2. Process 2 (not checked off)
   3. Process 3 (checked off)
3. Status 3 - not started

Is running a report and having the API pull from there make the most sense? If so, how would I remove the duplications since it's a copied row? Or is there something easier that I'm not thinking about? Maybe it's just pulling from two sheets?

prime_nathaniel

@CRana is there a reason you cannot have a field on your sub process sheet that is just index matched back to the original master sheet? your other options are to move it back (will incur some extra columns). You can also mesh the sub process approval back over. You can API the rows or approvals between the sheets too with webhooks (does not incur any extra references or columns). Lots of ways to do this with SMAR.