Display contact pledge/payment totals per campaign in detail view

AppSheet AppSheet Q&A
1

I have three tables: Campaigns, Contacts, and Transactions. The Transactions table references both a Contact and a Campaign (via a Category field).

When viewing a Campaign detail view, I want to see a list of all Contacts who have transactions for that campaign, showing:

  • Total pledged for THIS campaign

  • Total paid for THIS campaign

  • Balance remaining

The issue: The same contact can pledge to multiple campaigns, so I can’t just use simple virtual columns in the Contacts table (they’d sum across ALL campaigns). I need the totals to be filtered to only the current campaign being viewed, and this needs to work dynamically for any campaign without creating manual columns for each one.

Is there any way to build this in AppSheet? Or my only options are either to create a pdf, or have a physical join table with a row for each contact and campaign combination?

2

You need another table that joins Campaigns and Contacts - maybe named Contacts Campaigns.

This will allow a set of Transactions (and I presume payments) that are filtered and summed by both allowing you to easily provide sums for:

Total Pledged by THIS Contact for THIS Campaign
Total Paid by THIS Contact for THIS Campaign

1 Like
3

So I guess my assumption was right, the only way to achieve this is with another table.

Thanks

4

Yes. I kind of just glossed over that last sentence since I had the idea in my mind already. But I’m sure it’s not the ONLY way. It’s just the easiest and more efficient way.

For example, you could add 4 extra EnumList columns in the Contacts - Campaigns, Total Pledged, Total Paid, Balance Remaining. Then use indexing to store and retrieve the values for each Campaign - essentially a table within the columns. But then you have to deal with updating these values through indexes when transactions/payments are made. That would be messy.

Another possibility is to take advantage of Security Filters. You could build the app as if you were summing across all Campaigns BUT then design the app to filter by Campaign and Contact. With all the tables filtered, you would see summations only for that Campaign and Contact - but could NOT view multiple Campaign or Contacts together.

Last idea would piggy-back off the Security Filter concept. Use slices to filter the data. In some way picke the Campaign and Contact, store those choices and then use them to filter Slices. use the filtered Slices in the views that then provide the summed values by that Campaign and Contact combination.

I hope these help!!

1 Like
5

Thanks for your detailed explanation!

In the end I went with another table, that contains three physical columns, ID, Campaign, Contact.

I added an action that adds a new row to Campaign Contacts, and runs with a bot when a transaction is added, conditionally, if the row doesn’t exist yet, as follows:

ISBLANK(
ANY(SELECT(
Campaign Contacts[ID],
AND(
[Campaign] = [_THISROW].[Category],
[Contact] = [_THISROW].[Contact]))))

Then on the Campaigns Contacts table I added a virtual column that shows all matching transactions for this combination:

FILTER(“Transactions”,AND([Contact]=[_THISROW].[Contact],[Category]=[_THISROW].[Campaign]))

Then I extract the Pledges and Payments from this list:

SUM([Transactions][Amount Out])

And so on.

This isn’t end of the world, but I hoped that it wouldn’t be necessary.

I believe that in Supabase for example I could have a virtual joint table.

2 Likes