I’m creating an app to track the monthly savings of members of a community savings group. They meet each month to save a fixed amount. At the same time they can borrow small sums, make payments of interest from previous loans, payback loan capital and/or withdraw some or all of their savings.
I have a sheet with the details of each group, a sheet with all of the members and then at the moment I have 5 different sheets, one for each type of transaction and one sheet summarizing the data of each group.
The transaction sheets have the name of each member and months Jan-Dec. When the member enters the amount they save I would like visible only the current months column. Is it possible using the visible filter and the current date? What I thought might be possible would be to check current date and display the column entry element for that month if the date is after 1st of the month and before the end of the month?
Do you think my set up is the best way? The alternative would be have a sheet for each month and then have the 5 possible transaction types in each sheet for each member. But I’m still not sure with that how to make visible only the sheet for the current month?
So you are having months Jan - Dec as different columns?
Some screenshots might help us understand better. My first reaction would be to structure the data differently. Ultimately for simplicity, you could combine everything into one sheet, then have an email column, a transaction type column, a date column, and an amount column. However there are some advantages and disadvantages to having multiple sheets and separate columns for each month. The main advantage is less rows with your method, but the disadvantage is much more visibility conditions and separate layout configurations for each sheet.
Keeping with your current layout you can create a math column that will let you pick out the month number (Month(date)) from the current date. Then you can either use an IF Then column to copy each month’s values based on the current month to the result of the If Then column and only display that If Then column value on the screen. Otherwise, to can a second math column that pulls out the month from the transaction date…then create components for each month column and individually set visibility on each component to only show if the current month matches the transaction month.
3 Likes
Thanks for your reply Jeff, I’m back at work at the moment but I’ll take a screenshot and post asap and in the meantime consider your input before getting back to you. Cheers again for the reply!
1 Like
Here’s screenshots of the group sheet, member sheet and the saving’s sheet, other transactions mirror the savings sheet. Also on the app side I’ve included the screen with member detail and buttons for transactions and the form screen which at the moment displays all 12 months. As I said ideally this would display only the current month in which to enter the amount.
I like the idea of consolidating all transactions to one sheet but there are 39 groups with over 1,000 members. Each member saves every month (or should), that would mean over 12,000 rows just for saving. This is a free project for a charity so I’d like to minimise the rows used where possible.
I will see if I can implement one of the two suggestions you make for utilizing the current layout and come back with the results.
Thanks!!
So I’m a bit stumped.
I’ve setup a column on the saving sheet in Google sheets =TODAY() to display the days date. I’ve then set up and Maths column pick out the month number (Month(date)). I then set the component for January to display if the result is 1, Feb to display if the result is 2 etc. However the component is always invisible, what am I doing wrong?
I’m not clear on you comment on IF Then columns?
“Then you can either use an IF Then column to copy each month’s values based on the current month to the result of the If Then column and only display that If Then column value on the screen.”
The second part also isn’t clear to me.
“Otherwise, to can a second math column that pulls out the month from the transaction date…then create components for each month column and individually set visibility on each component to only show if the current month matches the transaction month.”
How can I match the current month with the transaction month BEFORE any data has been added? The component for the date to be added would need to be visible for the current month, which is sort of the problem. Sorry if I’m being daft here.
Could anyone advise on this?
TL/DR
Why would a component not be visible when set to “display if column value = 1?”
The column in question is a maths column converting today’s date from another column into the month number i.e. Jan = 1.
Thanks!
OK, trying to wrap my head around this. First of all, the problem might be where you have the current date column set up. It should be on the sheet that you used to display the Member transaction screen.
Also, you can avoid doing the formula in the google sheet by instead creating a math column, give it a value such as Month(NOW) and replace NOW it with Now. Like this:
Now that you have the current month number in the sheet used for the member transaction screen, you should be able to access that number when setting visibility in your form.
If you are still having problems, include some screenshots of your math column and the visibility settings, because I think it should work.
1 Like
Jeff, thank you so much!
Yes, my date column was on the transaction sheet. Changing the calculation from the Google sheet to the Glide data sheet worked immediately. Daft perhaps but I didn’t realise I could also use that calculation on the draft data sheet.
My final question on this then, being a guy who always like to understand the why, not just the what, is why will is not read the “1” when calculated from the google sheet? Any idea?
Regardless, I am indebted to you for your help with this one. Thanks again.
1 Like
