List containing common items and not common items in two lists

I have two lists budget and expenses. each list contains category and item. There is a possibility that one item is there in one list where as not in other. For example, User only listed budget for the item and didn’t log the expense yet. I want to show a table showing list of all items common in both list and items not common also. How can i do that?

Example
Two existing lists
Expenses
Item - A, amount - 10
Item - B, amount - 20
Item - C, amount - 30

Budget
Item - A, amount - 20
Item - B, amount - 20
Item - D, amount - 40

Result I am expecting

Item Budget Expenses Variance
A 20 10 10
B 20 20 0
C 0 30 -30
D 40 0 40

Strikes me as a database structuring question. If your example is the only functionality, you could have one list of items, each with a “budget” and an “expense” field. That makes it easy to display and do the variance calculation.

But I gather that you are letting your users create budget and expense items, and sometimes the budget item will exist but an expense item won’t, and vice versa, so they’ll need to be able to select existing ones or create on the fly. In addition, it’s possible that some items need to sum up into categories (like “grocery” would be a monthly budget item, but would have a lot of individual expenses that feed into it). So the right setup would depend on what your app is supposed to do, and how items are being created and displayed.

Use :merge and :intersect

This topic was automatically closed after 70 days. New replies are no longer allowed.