Hello everybody. So I have made a slice of a...

(Miranda Lubarsky) #1

Hello everybody.

So I have made a slice of a table.

The set up looks like the picture below.

How it is shown is exactly how I want to view the information.

All the totals for the plants on one sheet.

However, the totals that are there are the totals that were calculated with the numbers from the entire original table, not the slice.

I want a total of #'s JUST for the slice, not the whole sheet.

The whole point is that it will show how many plants my crew needs to put into their car at the beginning of the day for 3-5 clients that they are going to.

So it’s just those clients included in the slice, and now I need the totals to reflect that slice.

Can’t find anywhere to add a virtual column or anything like that to the slice, that would be pretty cool though as a future function (hint, hint).

When I go to the UX view of the slice and select COUNT for the group aggregate, again it counts the total from the original sheet, not the slice.

How do I get where I need to go???

Help is very very much appreciated. Thanks, Miranda

(Grant Stead) #2

Your formulas that create the slice should be the same you utilize in your expressions to total… Or you could create the totals directly referencing the slice as a table…

(Miranda Lubarsky) #3

@Grant_Stead How would I do that?

How would I reference the slice as a table?

Here is the thing, I am going to have several slices based off the same table, each of them using different columns, and I need the total based on the slice for each of those.

Right now the total is just calculated in the google spreadsheet.

But I don’t know how to get it to reference the slice.

Please enlighten me & thanks for your help!

(Grant Stead) #4

Yeah, nope… So, what you will end up having is a separate row/record for each plant/client assignment…

It’s all about relationships…

You have separate items… Employees Clients Plants Plantings (I think this is what you’re calling it)

Do you have an elevator pitch for your business so I can understand better what your do?

The plantings table is a joiner table… Just like how on your clients table your also joining them with an employee, in the plantings table you will

have a plant and a client… Then another row, plant and client…

So a client when you’re looking at a client, you will see all of they’re plantings, and vice versa, when you’re looking at a plant your would see all of the clients that have that plant…

(Grant Stead) #5

Your should add the clients in appsheet as new records… And once we fix your relationships and data structure you should no longer need to regenerate or work directly in your backend sheet… I hope you can see this vision…

(Grant Stead) #6

Also, maybe if it’s setup right you could give your clients access, and they can see comments and notes and pictures of their vegetables… Status reports, etc…

(Grant Stead) #7

Are you home front farmers in Connecticut! I want you to come and make me a garden! Except I’m in South Texas! LOL

(Grant Stead) #8

@Miranda_Lubarsky I spun up a very quick and dirty sample app for you to check out what I mean by relationships… You can find it on my partner profile, you should be able to look under the hood, and copy it, etc… It’s called farming. https://www.appsheet.com/Portfolio/297211

(Miranda Lubarsky) #9

@Grant_Stead Thanks Grant.

You see, it is not just me inputting this information, it is a ton of different people, and they are used to doing it on their computer, on google spreadsheets and this is not going to change.

I’m sorry, but it’s just the way it is.

SO I will always need to retain the ability to regenerate the information.

Can you please tell me exactly how this plantings table will work?

I still cannot envision it.

Can you tell me what the Column headers would be? I think I am getting that each entry would be the clients name repeated a bunch of times for each plant entry?

Is that right?

If I did have people inputting this information into a form in appsheet, how would I make it quick and easy for them?

It would be really annoying to have to keep selecting the client’s name every time you input a plant…but maybe you know something I don’t?

(Miranda Lubarsky) #10

Ok, so here is some of my plant information.

Would be great to create something where we could put together plant estimates for clients a lot easier than in the past so I included pricing although that is not what we will be using it for most often.

Most often, all I’m concerned about is the # of plants to bring to each client on any given week/day.

(Grant Stead) #11

@Miranda_Lubarsky did you have time to check out the sample app I put together for you… Of course it would need to be fleshed out with all of the details, but I’m hoping you would get the idea… To enter in a client’s plants, all you have to do is go to that client and when you add them the clients name is already populated…

So what is happening right now is you’re learning about database design. If you want a better future you need to be able to see it, and then utilize creative destruction to blow up what you have and replace it with something better… People are only using Google sheets as an input mechanism because it’s all they know or all they have been told to do… Appsheet excels at user input be it mobile or on a desktop… The hope would be that everyone would use the tool you build to send a new client a quote, find out where they need to be, and what plants they need to bring… The entire point of building an application is to make things easier and better…

I promise you that with what I’ve seen, you could make a beautiful and comprehensive application to manage it all through appsheet…

My recommendation is that you crank up a brand new app, with new data sources, and structure the data well, and start building something that is comprehensive. I’ll help you if you let me, but your existing data structure is going to be a huge fight, and it will not get you where you want to be.

(Grant Stead) #12

@Miranda_Lubarsky hrmmm well, from this and your other post I’m interested to see your column structure… I’m getting an inkling that we might be able to simply structure your data in a mate effective manner, and accomplish your goals… However, I could be completely off base… If you prefer, you can shoot me an email with screen shots of your column headers… grantstead@steadglobal.com

(Miranda Lubarsky) #13

@Grant_Stead Yes, I have been trying to get at this for weeks but it seems that some people that were responding to my posts before have dropped off and I don’t know where they went so maybe you can help me.

This is spreadsheet so you can get an idea:

Basically I need to organize this information in a few ways, from both columns and rows.

And frankly, I would prefer not to use any “show-ifs” if I don’t have to because I like the crew to be able to see all the information if they want to, but go to only what they need on a daily basis.

The rows are plants, varieties, and they each have a first planting date.

I need to organize these plants into their first planting date (represents the entire work week, so for example, for the whole week of 4/3/18, we are planting kale all week.

Not just on a Monday)

So I need to be able to view only the plants that we are planting on any given week.

Then the Columns are names of our Clients.

I need to organize the clients in two ways: first, by their assigned employee/gardener.

Each client is assigned a gardener in another table.

And then I also need it organized based on the day of the week that we visit them (Monday, Tues, Wed, etc.)

So basically, I want Mike, for example, one of our employees, to be able to see exactly how many plants he will need on a Tuesday, the week of 4/30 so he gets a total for each plant that is specific to the 4 or 5 clients he is assigned to go to on that day and week.

So in that original screenshot I have above of the app, I would like the [Plant #] column to show the total of Mike’s clients on that specific day.

I know that app sheet is unfriendly to the two axis, and it is only built for tables.

But no one has told me how I could split this table up.

I am finding it impossible to envision it.

Because every client has a different number of each plant that they need.

It is not the same across the board.

Please please help and thank you!

(Grant Stead) #14

@Miranda_Lubarsky this is beautiful! Yes, your data is not organized correctly. You do in fact need multiple tables. 1 table for your plant types/pricing another table for your list of clients, another table for your list of employee/Gardner’s

And then your need some sort of “assignments/plantings, etc…” I need to talk with your a bot more to understand the business objectives there…

So outside of appsheet if you can start getting your tables built, I can then review them, clean them up, and then show you how to do the relationships in appsheet… Once you follow a good data model your will see that appsheet stops being such a fight, and actually starts helping you!

Everything you described, and what I think you need is very doable… (Also, I don’t charge for education! So don’t worry about that!)

(Miranda Lubarsky) #15

@Grant_Stead Thank you so much for your help!

I REALLY appreciate it.

Can you explain to me how I will make a table to organize the numbers of plants that each client needs?

This is the one I’m having trouble visualizing.

I actually already have a client contact table with a column listing assigned employees and another column listing the day of the week that the client will be visited.

It is already a part of my app and works well.

It is often edited in google sheets, so I regenerate from time to time to update information.

Here is a view of some of the column headers to give you an idea.

I’m not showing the clients listed for security reasons.

(Miranda Lubarsky) #16

@Grant_Stead Here is my employee table headers.

People & their info is listed below, again not showing for security.

(Grant Stead) #17

@Miranda_Lubarsky so, one thing to consider is that once you start using appsheet you need to try to leave your backend data alone… I’m not sure if you mean regenerate or sync… But, once you build there app, your shouldn’t sort/filter your sheet, or need to be regeneration

(Miranda Lubarsky) #18

@Grant_Stead If I transpose the information in that first big spreadsheet I showed you that we are trying to break down into tables, I can put the information into something like this: The client’s name is listed in the first column and there are numbers in all the other columns (plant columns).

But when I view this table on app sheet, I can never get it to show the information how I want it, with the plants & their totals for only the clients that I want listed in one place so someone could quickly view it.


(Miranda Lubarsky) #19

@Grant_Stead I need to be able to regenerate over the course of the season because we are constantly getting new clients.

All that info is uploaded on google sheets and I regenerate occassionally. No way to get around this.

We need it as a company.