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

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

0 18 620
18 REPLIES 18

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โ€ฆ

@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!

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โ€ฆ

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โ€ฆ

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โ€ฆ

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

@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

@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?

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.

@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.

@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

@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!

@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!)

@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.

@Grant_Stead Here is my employee table headers.

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

@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

@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.

Thoughts?

@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.

Top Labels in this Space