Virtual spreadsheet?

[Pratyay_Rakshit]

Couldn’t find a more precise name for the question.

There are projects, and as expenses are entered on account of those projects, under different ‘Heads’ such as material purchase, labor, subcontracting etc., there is a virtual column defined for each project to show the total cost incurred for the projects. Which is essentially the sum of amounts under all those ‘heads’. The column is shown on the detail view of the project.

Now I want to show the expenses under each such head. It wouldn’t have been a problem if the number of ‘heads’ was small (like within 3-4) - it only would have taken me 3-4 additional virtual columns on the detail view of the project.

But the number of such ‘heads’ is substantial, and having them all in one detail view would be a disaster for aesthetics. So what I want is to have an action that takes me to such a detail view which would show all the virtual columns and the associated expenses.

Is there any way to make a whole sheet virtual? Or any other way which can serve my purpose?

[LeventK]

You can’t create a virtual sheet or virtual table. For your purpose you can use LINKTOFILTEREDVIEW deep link basically. We can have better propositions if we know what’s the idea, what’s the table schema, how the data are related etc.etc.

[Pratyay_Rakshit]

Well let me elaborate.

Just know, every expense I will talk about in this thread is under a project. For reference and simplicity, say there are two projcets P1 and P2.

Expenses are recorded through three tables.

1. There is a user expense submissions table, which records expenses for users. There are mechanisms to approve or reject expenses. Only ‘Approved’ (there is a column for that) expenses for users should be recorded in any aspect, be it the general ledger of company, or in this case, the project ledger. The ‘ref’ columns in the user’s data are user, project and the payment account. They can be summarised using those three criteria.

1.A. Right now there are not many users (12), and I have each of their data for the project using linktofilteredview. The sum and the break up.

2. There is a company direct expenses table, which records payments for irregular (less often) suppliers, subcontractors. Suppliers and subcontractors have bills which are entered per project, the payments are entered against the bills and the bills have status ‘paid’, ‘unpaid’ and ‘partially paid’. Since the bills have direct relation to projects we can specify supplier status for projects based on the bill status. Like supplier X has bills of 10000 to project P1, out of which 7000 is paid (bills marked paid) and 3000 is unpaid (bills marked unpaid, and the only bill marked partially paid - the general payment tendency wouldn’t be to have multiple partially paid bills).

The direct expenses table records other expenses such as petty expenses related to project, illicit payments (they are substantial, and I’m looking for a way to ‘mask’ the expenses recorded under illicit payments, but that’s a discussion for some other day), and all other expenses to a project.

Additionally, there is also a ref column to any company expense, which records the type of expense (like illicit payments) I call them cost centers and they are the footsteps towards the accounting system that I am yet to build using Appsheet. I never liked being spoon fed by QuickBooks or Tally, so I made it my pursuit to build my own system when I first truly recognised appsheet, and understood its working mechanism.

3. There is a payment sheet for a frequent class of suppliers, now only one (ready mix concrete) but is sure to change in the future when we take up new class of projects. Then maybe there would be a switch to mark a supplier ‘frequent’ and have a different mechanism for that. Their bill entry table is also separate, and their treatment is exactly the same for suppliers, except for the only fact that one has to work on them a bit more.

Now the summary columns that I would like to have are RMC (will decide later between total bills or total paid bills), suppliers (with companywise breakup), general expenses (with areawise breakup, ofcourse the breakup points are there within the table schema) and illicit payments.

They all have their sub classes.

My visualisation is an action on the project detail view -> Sub actions pointing towards various types with linktofilteredview -> even sub-actions accounting for further breakups.

Kind of like a tree.