Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About halfdome

  • Rank
  1. I have a database of Donors I have created a “Dashboard” type view that I would like to add some portals to display useful information on the “Front Page” of the database. For instance, birthdays of donors, Annual donors “month-before donation due” reminder, and “Late” donors, to name a few. To accomplish this, I’ve created Table Occurrences for the desired Table::field and created a relationship to a global Dashboard::MatchField to create this “query relationship”. I’ve successfully accomplished this for the birthdays and “month-before” reminder listed above. Where I ran into trouble was with “Late” Donors. The field that I’m attempting to create the “query relationship” with (Partners::DonationStatus) is an unstored calculation, which, if I understand correctly, will not work to display related records in a portal. What the field calculates is the status of Monthly, Annual, and Quarterly donors based on their last donation date (For instance, if the donor is a monthly donor and their last donation is > 31 days ago, they're "Late"). I thought about creating multiple criteria for the relationship between the TO and multiple global Match Fields to essentially replicate the calculation field, but that wouldn’t work because everything is AND in these relationships, so I couldn’t create different conditions (Monthly > 31days OR Annual > 365 days OR Quarterly > 91 days) for all the different partner types in one relationship. I wouldn’t object using this method to make this work, but I would really like to display all three types of Donors in the same portal, not three different ones. Is there a trick or tip that can work around this problem? I could easily perform a find for this data, but it would just be so much more convenient to have an “at a glance” view of it when the database is first opened.
  2. I am trying to create a chart that I thought would be so simple...and now I'm down this rabbit hole trying to make it work. I have a database is a record of all our donors. What I am looking to do is create a chart that will display donation info by month on each donor record. I have four tables: Donors, Donations, Correspondence, and a join table called DonorCorrespondence. Donors and Donations are related through the DonorID field. Specifically, I'm trying to set up a column chart in a tab panel on a form view layout of the "Donors" table that will display data from the "Donations" table. The y-axis data is the sum of all donations in a given month by a specific donor and the x-axis is month. This post is the model for how I am going to set up this related record chart. There are two additional elements that I'd like to have for this chart that I haven't been able to figure out for this situation: 1) I would like for it to dynamically display only the last 12 months of donation data 2) I would like for it to display the month (x-axis) even if there are no donation records for that month. For example, say I have a donor named John has given $100/mo for the last year. But one month, no donation comes from John. My chart would not show a month as blank for that month, it just wouldn't display anything for that month at all. Only months with donation records appear on the chart. Something like "JAN FEB MAR MAY" if April didn't have a donation. What I'm looking for is to have a visual cue as I flip through my donor records that they may have not given on a particular month. I've seen posts that at least somewhat relate to both of these questions, but not in the context of a related records based chart. Thanks for any help with this!
  3. Ok it all makes sense now. Super clever solution, thanks for your help!
  4. That works great, what a clever solution! Thank you so much! I'm a curious guy by nature, and after looking up the Mod function, I'm still trying to figure out what going on in " Mod ( DonorID ; 2 ) ; 2 ) + 1 ". Would you be so kind to explain what's going on in that so I can understand it and possibly use it for myself in the future, if need be? Thanks again for your help on this, I really appreciate it!
  5. This database is a record of all our donors. There are four tables: Donors, Donations, Correspondence, and a join table called DonorCorrespondence. Everything I described in the OP is in the "Donors" table. We like to send correspondence to each monthly donor every other month, and to accommodate this, I've created the Group ID field. Groups 1 and 2 are our monthly donors. So Group 1 would get correspondence one month and the next month Group 2 would get correspondence. Irregular donors will be in group 3. The idea behind the groups is that I could perform a find that looks for all donors from, for instance, Group 1 and any Group 3 donors in the last month to give me a list on who to correspond with on a particular month. We have around 90 monthly donors (that's why I want two groups of 45), but around 120 donors in the database. Using the "DonorID" serial number field in the Donors Table to calculate the groups won't work because the 90 regular and 30 irregular donors are intermixed throughout. So the thinking behind using the "DonationMethod" field in the Donors Table is that anyone who has an entry in this field is a monthly donor. If I could do a count of all non-blank occurrences of the "DonationMethod" field and run it through the Case function, I could have the "GroupID" field in the Donors Table automatically populate the group number for me. The first 45 would be Group 1, 46 and up would be Group 2, and all irregular donors would be Group 3. Sorry for the lack of detail in the first post, I hope this helps shed some light on what I'm looking to do.
  6. Hi, I am trying to do something that seemingly should be very simple, but I cannot figure out how to accomplish it. I have a field that I am trying to have calculate a number based on certain properties of other fields. This field is called "groupID" and should display either "1", "2", or "3". Where I'm running into trouble is in defining the "1" or "2". Basically what the equation is in layman's terms is that when the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is ≤ 45, I want it to display "1" in the "groupID" field; if the total number of non-blank records in the "DonationMethod" field in the "Donors" Table is > 45, I want it to display "2". I know the Case function will work to evaluate the tests that give the results, but I can't seem to find the correct tests. I've tried the Count function, the ValueCount function, and a few others as my tests, but they are not giving me the results I need. They seem to only evaluate whether a value exists in the particular record I'm viewing in Form View, where I'm looking for it to evaluate the field in the entire "Donors" table and give me a count of all non-blank records. Thanks for any advice you can pass along!
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.