I have two charts for my students to use on a tab. Using a form button they can “set up” the charts using a form tab to set the chart title/description, the number of items they will be counting in the bar chart and set the labels for those items. Works fine as long as they only have one row on the sheet capturing this information. But the problem I have is that when this information is brought into the user sheet via lookup, it will only display/capture the first row of entries for their email address(email relation). But I need it to be so that when they come to class the next day and want to make a new chart/overwrite the old chart data, that the new data/latest row for their email address is being picked up by the user sheet lookup. Any ideas?
You can use a combination of multiple match relation - single value column to pull over the latest entry.
So I would make a multiple relation using timestamp column on the destination sheet to the user sheet. Bring in all the time stamps and then use a single value to pull over the latest entry? Currently I don’t have a timestamp column in the user sheet to make that relation.
Or would I need to use a template to combine the users email and timestamps first-then pull it over with the single value column?
I assume you have the user emails in both sheets? You can just make a relation from those.
yes, I do. Once that relation is made, do I make a lookup of the timestamps? But lookups don’t allow for match multiple
ah ok, I see i can use a rollup to get the latest instead of a lookup. Now that I have the latest time stamp. how do I use that to get the latest entry of data?
You can simply opt for the single value column way to pull the latest info available from the relation so you don’t need the latest timestamp. Would it work for you?
Here is what I have so far: Relation_6(2) that is a match multiple using email address. Then New Column D is a single value column asking for a rollup of latest timestamp. Not sure what to do now to be able to lookup of the latest row of data for each user…such as the information of those items in the 3 middle columns
No, I mean the “Single Value” column not a “Rollup” column. Seems like you’re still using the rollup?
Ahh yes, ok, switched it to a single value. Now how to grab the latest row of information. For example I want to pull in/lookup # or items, chart description?
You should find an option to take the “last” value of the column from the relation. Can you find it?
Ah yes, thank you. Now I get it! doh. way easier than i imagined. Thank you! Very handy for the future indeed!!
Yeah, before they rolled out this single value method, I had to do something like what you did, latest timestamp, then another template column and another relation - lookup. Way longer than I want!
Yea, that is what I was thinking was brewing for me: this is super clean.
Turns out this didn’t work for me after all. Whats happening is that every time a student enters measurement data for the chart categories it is applying that entry across all single values based on the user email relation. Thus, things like the title of the graph, # of categories, notes get wiped to empty because those are not being filled out every time a new set of measurements are recorded…trying think of a way now to separate those out so they don’t get wiped…ahh, maybe write those to a different sheet and make a email relation with that sheet…ok, will try it…
Ok here is how it turned out. Title and # of categories designation on 1 sheet, category labels on another, sample data on the same as the chart component. Category labels brought into sample data sheet with a template from the user sheet relation to the labels sheet.
Labels are not lined up with the columns but after trying bank spaces and other means, best I could get.
When a student wants to change things, they can use the form buttons which are using single value to show the latest.
