How to show only Expense or Sales Categories from an Enum based on value of another column

[Stephen_Mattiso]

Howdy! I’m making a bookkeeping App for a non-profit Club. I want to keep it simple and record all transactions in one Table, including Invoices. I have an Enum column [Category] with all Expense & Sales Account Categories. I have an [Invoice] Enum column with one button choice of “Invoice”. When “Invoice” is entered into [Invoice], I only want the four Sales-type choices to be available in [Category] Enum column. When “Invoice” is not entered in [Invoice] column, I want only the twelve Expense-type Categories to appear.
Related question: I also need to only allow positive currency amounts to be entered into [Amount] when the transaction is a Sales type/when “Invoice” is entered into [Invoice] column, and only allow negative amounts into [Amount] when [Category] is an Expense/when “Invoice” does not appear in [Invoice]. I’m using this 'forced" positive or negative [Amount] setup because that’s what I see most bookkeeping software do, however, it seems simpler and like the App would be able to handle all [Amounts] entered as a simple positive Dollar amount and then automatically make it a negative only if it is an Expense type category.
I’m still working trough the best set up for this, so any suggestions would be much appreciated. TYTYTY!!

[Suvrutt_Gurjar]

Hi @Stephen_Mattison,

Just sharing some ideas that I worked on a test app based on my understanding of the description you have given.

Hope this helps in some way.

Here are some screenshots for a quick reference,

Picture 1: Summary View of monthly expenses

Picture 2 Example of a month with profit

For denoting expense amounts with a negative sign, there is format rule with " - " minus sign.The user need not enter negative amounts in the form. The back end sheet also stores teh expenses as positive numbers.

The Profit/ Loss column is calculated by subtracting total sales ( a VC) from total expenses for he month.( Another VC)

Picture 3 Example of a month with loss

Picture 4: Form view with invoice enum selected. There are two more dependent enums ( Expense and Sales Category) for categories. These Sales/Expenses enums show up based on invoice enum selection.

The combined category column in table view in picture 3 above is computed based on what category is non-blank ( Sales or expenses) for that particular record.

Picture 5: Form view with invoice enum deselected.

[Stephen_Mattiso]

This looks great, TYTY. I will dig into it & report back here.
Here’s what I did already that seems to work pretty good, for now… I just used 2 Enum columns, one for Expense type categories & one for Income categories. I made these columns appear or hide depending on whether “Expense” or “Income” are entered into [Expense or Income] column. At first I wanted to only use one column for all Categories, but the more I play with this, the more I like 2 columns.

[Suvrutt_Gurjar]

HI @Stephen_Mattison,
Great. Yes, two enum columns categories , one each for Sales and Expenses was exactly what I also thought.

Another suggestion was on your need to show expense amounts as negative without user needing to punch in negative sign. That can be accomplished by using format rules.

[Stephen_Mattiso]

Hi Mr G! Thanks so much for your kind help! Sorry for my delay on proper response, I was in Chicago this weekend at an important family gathering, didn’t even bring my computer! Haha! I’m really am just now able to look at you thoughtful solutions.
As I briefly said above, I have basically done what you’ve suggested, used separate columns for Expense & Sales Categories. I omitted the [Invoice] column that I was using to decide how Categories appeared and how transactions are handled. I have added in it’s place a column [Income/Expense] with “Income” or “Expense” as the only choices.

In the past, I have used a separate Table for Invoices, but I am wanting this App to be insanely simple and streamlined, so I am going to do everything in one Table. I am (currently) planning on adding back the [Invoice] column to tell the App to make & send an Invoice for this Row/Transaction.

Thanks for the tip on using Formatting to make the Expense Amounts negative. I really dislike software that forces User to enter these as negative, this seems inefficient, clunky & weird. I don’t feel like I spend negative money & don’t want to have to flip my brain every time I enter an Expense transaction. I understand that my software needs to see it this way, but it can format my data entry after I enter it.

[Suvrutt_Gurjar]

Hi @Stephen_Mattison,

Thank you for your response.

I am aware you have been contributing in this great community for long. I have benefited from your informative posts in the community. So I am pleased if any of idea sharing is useful to you.

I fully agree with your thinking on inputting negative amounts and making the app design as simple and intuitive as possible to the end user.

Have a great week ahead.

[Stephen_Mattiso]

Suvrutt_Gurjar:

Another suggestion was on your need to show expense amounts as negative without user needing to punch in negative sign. That can be accomplished by using format rules.

Thanks again, Sir for this tip, but I do not see how in Format Rules I would say IF[Inc.Exp.]=“Expense” to make [Amount] a negative number entered into the Database?
To be honest, I would be happy to keep it all positive numbers, if I were not afraid some Accounting guru would crap his pants over it! lol!
TYTY!

[Suvrutt_Gurjar]

Hi @Stephen_Mattison,

Yes, you are right. Format rules cannot make a number negative in the database. I have specifically mentioned in the above thread that
“For denoting expense amounts with a negative sign, there is format rule with " - " minus sign.The user need not enter negative amounts in the form. The back end sheet also stores teh expenses as positive numbers.”

I also mentioned above that “need to show expense amounts as negative without user needing to punch in negative sign.”

So essentially the format rule suggestion was to visually “show” the expense numbers as negative in the app views without the user needing to enter a negative sign. In the back end they are not negative. We can manage back end with expressions to subtract expenses from sales amounts.

[Stephen_Mattiso]

Yes, I am very sorry… I read your earlier response too quickly, and then waited too long to make my last comment. I see where you had already answered what just asked, haha, doh!!
You and I are thinking alike. I am happy with everything positive in DB, easy to make App do math, haha! I think I will just keep the App positive numbers and help Users to adopt a cleaner & more intuitive (for the average money-spender) UI. Thanks again!!

[Suvrutt_Gurjar]

Hi @Stephen_Mattison,

Nice to know the updates. yes, keeping everything positive in DB makes sense. All the best.

Please do not say “sorry”. We all in community are learning / getting tips from each other. At times, we all err, when reading long posts that involve a bit complex technical discussion.