Data from different tables in bar chart

Ask the Community Get Answers
1

Hi -

I work with a non-profit organization. We provide boxes of food to families in need. I have developed three different tables depending on how, and to whom food assistance goes to.

  1. Table with Food Boxes Delivered to Registered Participant.

  2. Table with Food Boxes Delivered to Non-Participant.

  3. Table with Food boxes to Distribution Partners (other organizations).

Records in each table include:

  • Name
  • Date
  • Zip code
  • Number of boxes received.

I want to create a bar chart which shows, for a given date (From filtering)

  • Total Number of boxes going to registered participants
  • Total Number of boxes going to people not-registered
  • Total number of boxes going to other organizations

I was able to create an extra table with sum of ALL services from the three tables. However, I have not been able to figure out how to get it to filter by date.

In excel, I would have used a countifsformula to show # of boxes delivered, for a specific type of recipient (Participant, non-participant, distribution partner) for a specific date. However, i cannot figure out how to do this type of conditional formula in KNACK.

Any other ideas?

Jacob

2

It seems to me that your current challenge stems from your choice to split the deliveries into 3 separate tables. Have you thought about instead of having 3 tables consolidating them all back into 1 table? Add a multi choice field* which defines the type of delivery. This should then allow you to filter and sum however you want, by date, zip code, delivery type, etc. Also way more practical for the future … no need to add another whole table if you get another delivery type.

  • In fact i would recommend you define the delivery type using a connection to a separate reference table called delivery type. That way admin users can add new delivery types through a simple admin form instead of through the Builder. Plus you can include filter fields to the delivery type reference table such as Active? (Y/N) to control which delivery types can be selected in dropdowns. Or add a field which can be used to restrict which roles can see which delivery types. Way more flexible for controlling access and for totalling.