How can I track form completion?

VictoriaD

Is DataMesh unable to match Look Up Values that are the result of a formula? My DataMesh wont run and am looking to see if there might be an alternative work around.

I have a Triannual Report form that needs to be filled out across campuses. The Campus representative must be able to see and review each report completed by various programs at their campus. They must also follow up with anyone who hasn't completed their form so I have a tracking list for them and for us internally. Campuses should not be able to see each others statuses. Projects should not be able to see each others responses. I cannot simply assign forms/rows to specific people because the submitter can change frequently.

I am probably overcomplicating all of this, but this is what I have tried so far.

I have a Triannual Report that projects at various schools are completing. There are some projects that exist across multiple campuses. On the form I have logic to bring up specific projects at each campus and then a second logic set of questions based on the project selected. These submissions are then checked on a campus level via Dynamic View. In the Dynamic View I have Campus representatives reviewing the submissions which updates the row status.

On the internal sheet, I have a column that creates the unique identifier for the row " = (Campus) + " " + (Project) " to get the Campus Project name.

I have a separate Completion Status sheet by each campus/project across the year. Each campus and project is listed. I wanted to data mesh the status update but DataMesh can't recognize the names as the same.

I then changed how the Completion Status sheet created the names to match how the Triannual Report are generated. DataMesh still couldn't recognize the names.

Now I'm thinking I need to do an IF INDEX MATCH across sheets but I can't seem to get a formula to work. Although I'm probably doing something wrong as I don't totally understand how this works.

=IF(INDEX({SAPEP Triannual_ Fall 2024 Range 1}) MATCH([campus/program]@row, {SAPEP Triannual_ Fall 2024 Range 2},0)

Alternatively I'm thinking about how i could use a dynamic view. Maybe I could have the sheet prefill all of the campuses and programs and have them fill it out through Dynamic view, but that looses the Logic and will present them with questions they don't need. Even if i did personalized dynamic views by project with the questions specified, I'd have to go through a lot to get sharing access done properly because it's not very straight forward as to who fills out the Triannual Report.

Maybe I just need to suck it up and make all campus/programs a drop down option and go through and redo my Logic Paths for my form? Then Data Mesh will be able to run?

Is there a better way to do this?

Brian_Richardson

Data Mesh can definitely match against formula-derived values. So if you have a formula on one sheet that's putting together Campus+Program and another sheet with the same Campus+Program values, it will match them. If you use Copy and Add mode, it will match and update the ones it can find in the destination, and will add the missing items from source to destination.

It has to be an exact match though. So in your second screenshot there's no match to the items in your first screenshot. It can't be a "contains" kind of match, has to be precisely the same.

If Data Mesh isn't finding matches then the issue is in your data somewhere, the values aren't identical.

If you have people using a form to submit your Triannual status, then that form submission creates a new row each time. If you have two submissions therefore on the first sheet, Data Mesh isn't going to pick the latest one, it's going to match the first one. So that may be why you're not seeing the results you expect. It can only either match the first duplicate source row it finds, or none at all in the case of duplicates.

Like you noted, you could instead use INDEX/MATCH to pull in the status that you're looking for, instead of Data Mesh. It does a similar process of matching some value on the source sheet and bringing it over to the destination. You're getting UNPARSEABLE because you completed the INDEX/MATCH piece of the formula but you stuck an IF in front of it and then didn't complete the IF arguments. You likely just want to drop the IF piece and let your INDEX/MATCH run and fill in the cells. Note, though, again that if there are two source rows with the same Campus+Program, the INDEX/MATCH is only going to find the first match and return it.

If you need it to return the last match then you need an INDEX/COLLECT

=INDEX(
        COLLECT({SAPEPE Triannual_Fall 2024 Range 1}, {SAPEP Triannual_Fall 2024 Range 2}, [Campus/Program]@row),
        COUNT(
              COLLECT({SAPEPE Triannual_Fall 2024 Range 1}, {SAPEP Triannual_Fall 2024 Range 2}, [Campus/Program]@row))
                  

The formula above says "Return the collection of "range 1" results where the campus/program matches and then pick the last one (the one that's numbered the same as the count of all results)"

VictoriaD

Thanks, I'll give it a try and check the dropdown options that composed my formula. The screenshot didn't show i had those same item names because i wanted to show the rejection i was getting. Maybe there's some small inconsistency (I'm thinking maybe spaces added somewhere I'm not seeing..?) with how they were typed as the source sheet was made at a different time by someone else before they started building integrations and didn't yet have normalized nomenclature .

Yes the single match has been stumping me, but I'm thinking of playing around with "= Campus + Program + Round " helper columns if i can get this match to work!

Thank you!

VictoriaD

I'm still unable to get this data mesh to run. The drop down options are copy/paste between sheets so its all the same text pulling into my formula helper columns. I can't figure out why DataMesh isn't seeing any matching items. Any ideas?

Sheet 1 (form submissions)

Formula:

=Campus@row + " " + Programs@row + " " + [Reporting Period Drop]@row

Sheet 2: (Tracker)

Formula:

=Campus@row + " " + Program@row + " " + [Fall Period]@row

Data Mesh

Brian_Richardson

It looks like the Campus/program on the first sheet has a line feed in the middle. Fall 2024 is shifted down a line where it should be wrapped on the same line as (EAOP).

Although invisible, that character will screw you over for matching.

You can clean those out with a SUBSTITUTE([Campus/program/period]@row,CHAR(10),"")

Brian_Richardson

In the first sheet it looks like there's a Line Feed in your campus/program/period (?) column. Fall, or maybe Fall 2024, should be showing on the same line as (EAOP). I see your formula isn't adding a line feed but I'm wondering if the multiple selection dropdown value includes it.

Try

=SUBSTITUTE(Campus@row,CHAR(10),"") + " " + Program@row + " " + SUBSTITUTE([Fall Period]@row,CHAR(10),"")

Brian_Richardson

In fact I just did some testing and multiple selection dropdowns definitely add a Line Feed invisibly into the combined text. The Substitute should strip that out and make it match.

Or you can make the column not a multiple-selection dropdown but rather a single selection and avoid the issue.

VictoriaD

You are a genius! The multi select vs not made the codes read differently!

The multiselect acts like a CHAR(10) adding a line break. I hadn't noticed since i keep my cells rather small.

- but then when i added the CHAR(10), into the other sheet it still didn't read in Data Mesh since the formulas typed technically in don't match even if visually it has the same outcome.

I had to change it to match as single select on both sheets and it's finally working!!!!

Brian_Richardson

Yay!!! Both learned something new today!