Help with Database architecture

Panorama X
1

Happy Friday and thanks for reading.

I’m building a database to track volunteer hours for our school’s Parents’ Association. Hours are tracked by family, and so the database is made of records that include the kids, along with Parents 1 and 2, plus skills and availability for both parents, contact info, hours worked and hours due.

Tracking the hours by family is working great. Where I’m coming up short is that we’d like to search something like “baking” and only see the parent who has that skill, not both parents in one long record. Right now, if parent 1 skills includes “baking” and parent 2 skills doesn’t, you get the whole record.

While you can look to see which parent actually has the skill in question, it’s harder to select the correct email addresses quickly. (Ideally we’d like to select all the correct email addresses and easily copy them to a bulk email as BCCs.)

UPDATE: Per the @JamesCook question below, the way the form is setup is that there’s a (long) series of checkboxes, and clicking one adds the skill to a field called P1Skills or P2Skills, building a list of skills. So P1Skills might look like “baking, decorating, accounting” and P2Skills might look like “web design, data entry.”

Some questions:

  1. Is there a way to do this without breaking family records up into individual parts? If there is, I’d rather do that.

  2. If no, is this something I would use a “relational” database setup for? For instance, create a Parents database where every parent has their own record, and link those records to the family record they are a part of in the main database? (And could two different records in the Parents database be linked to the single record in the main database?)

I also realize the best approach might be none of the above. Thank you for any thoughts/direction, and have a great weekend!

Best,
Biagio

2

Without knowing how your parents are separated and keyworded by their skills, within a record, my inclination would be to use arraybuild to load a variable. ( to my thinking arrays are one of the best tools in Panorama ) The formula could evaluate IF parent1 skills contains “baking” then import() email address.

Use commas as the separator, or Returns, then replace with commas.

The resulting array can be placed on the clipboard or saved as a text file.

1 Like
3

Thanks James,

That sounds great and I’m going to start reading up on your suggestions now. Copying the correct email addresses to a text file would work well.

To answer your question, the way the form is setup is that there’s a (long) series of checkboxes, and clicking one adds the skill to a field called P1Skills or P2Skills, building a list of skills. So P1Skills might look like “baking, decorating, accounting” and P2Skills might look like “web design, data entry.”

Thank you very much for the direction, and have a great day.

Best,
Biagio

4

So something like this would work:

Local lvEmailList
ArrayBuild lvEmailList, ¶, “”, strip(?(P1Skills contains “baking”,P1Email,“”)+¶+?(P2Skills contains “baking”,P2Email,“”))
lvEmailList = replace(lvEmailList,¶,", ")

Then you save it or paste it

1 Like
5

Thanks so much, James! Really appreciate how supportive you and the community here have been.

Thanks again,
Biagio