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

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

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.

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.

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.

1 Like

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.

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.