Relation Among Sheets

Hello! So I’m having trouble trying to connect different form answer sheets :confused:

The app is organized in N categories and each category has X forms. Let’s get category Meat: this category has two forms, so both buttons show up on the same screen. Each form submits the answers to the same answer sheet that is Meat Replies.

The way the sheets are organized is one with all the categories, and others with the replies for each category. So for example, in the category sheet there is Meat and Packaging, and then I have Meat Replies and Packaging Replies. On each reply sheet, there’s a category column, so I can relate them to the Category Sheet and vice-versa.

Problem: because of this connection the forms are linked to the categories sheet and not to the reply sheet, so when I want to apply a visibility rule based on the answer, for example date submitted, I can’t because this information is stored on the replies sheet and not on the categories one.

Any idea how I could make this work?

Couldn’t you create separate relations and lookups from the categories sheet to each form reply sheet, then set your visibility based on each of those lookups?

That’s halfway done! The problem is one of the values that should be lookedup is a “date math result” and instead of giving me an integer on the new sheet, it gives me a date in number format

Do you have a screenshot? Can you clarify what the lookup value looks like? Are you doing the date math with a sheet formula or with a glide math column?

I’m doing the math on the glide math column, because I couldn’t figure out how to do it on sheets (the now formula format and the date stamp format are different, and it’s impossible to change any)

The formula on glide is NOW - Date stamp, and the result is an integer.

There’s a screenshot from the math column and from the results I get with the lookup

Screen Shot 2021-02-03 at 5.24.55 PM

Ok, now I understand. So you want the result of ‘5’ (which is the number of days between two different dates) in your lookup, not an actual date, correct?

Whenever you set precision on the column, it’s only a cosmetic change to the column so you can display it nicely to the user. It does not change the underlying value. What you want to do is wrap your math formula in a ROUND, FLOOR, or CEILING function. Whichever you use depends on the result you want. Would 5.4 days count as 5 days or would it count as 6 days? Round will round up or down based on the decimal value. Floor always rounds down. Ceiling always rounds up. By wrapping it in one of those functions, then the true underlying value becomes the rounded value and setting precision on the column has no effect. Then the lookup will pull in the true rounded value.


Thanks a lot! @Jeff_Hager

1 Like