Missing a table?


#1

Hi all !

I’m in the final of configuring a performance (musician’s) BD. Very excited about all the news possibilities :slight_smile:

This is my problem:
I am assigning concerts to groups made of 2 or 3 musicians. I have a table for the VENUES, a table for GROUPS and a table for the MUSICIANS. I also have a table I call CONCERTS that bridges GROUPS with VENUES along with other details. The tables GROUPS and MUSICIANS are linked.

I now want to prepare a report where I could calculate the wages of the individual MUSICIANS.
Do I have to create a new table for this or is there a simpler way ?

Thanks !
F.


Automatically creating and updating new tables based on a record
#2

I suspect the answer is “it depends.” What variables go into calculating the wages? For instance, are all musicians paid the same base rate, or are some more highly paid than others? Are they paid per hour or per concert? Are some venues or groups considered more prestigious – and thus better-paid – than others? Are they paid only for performances or for rehearsal time as well? Each of these variations would impose different requirements on data architecture – and, most likely, each could be adequately supported by any of several different designs. If you can provide a little more detail, it would be possible to offer suggestions…


#3

Thanks for your reply @W_Vann_Hall !
Yes, the musicians do have different wages depending on the length of their concert and whether they are singers or musicians. Other than that, they are paid per concert on a fixed rate and in this case, they are not paid for rehearsal time.
Thanks for helping me out, very appreciated :slight_smile:


#4

I think you need the extra table (maybe called WAGES? :blush:), linked with Concerts and Musicians. I am not sure how to deal with Groups :thinking:


#5

Thanks @Elias_Gomez_Sainz, this is exactly my concern as well :sweat_smile:


#6

The Groups are always the same? Or every Concert is performed by a “new created” Group?


#7

Yes, groups are the same for a given year…


#8

I would do something like this. I think that you can’t automate the process to add the wages for every concert with the current Airtable features.

The scheme:

The base:


#9

Wow, thanks @Elias_Gomez_Sainz, I will give it a go. Thank for your help :slight_smile:
Do you think it would be possible to filter the wages by month ?


#10

Yes! You could Lookup the date from the Concert, or add a Date field for every record in Wages. then you could get the month with a Formula field, and then Group by that field.

I’ve updated the base to add the Date field, and the a Month and Year to do the grouping. I have added another View in Wages for the dates.


#11

You are so kind, thank you very much ! Could you let me see how you coded the field month and year of the wage table. I can seem to access the menu.
Thanks !!!


#12

You have MONTH() and YEAR(), not so hard :sweat_smile:


#13

Sharing an Airtable base via shared link (as opposed to making one a collaborator) provides read-only access to the base – which means one cannot view field configuration. However, unless the creator has expressly blocked it, one can make a copy of the base to one’s own Airtable area and have full creator rights over the copy.

When I share a base, I usually document formulas and data types in the field description, which can be read by someone with read-only access. Sometimes, though, that’s just not possible – like right now, when fully documenting what I’ve done would burn another hour or two. (Heck: Figuring out what I did would probably take an hour all by itself.)

I’ve smacked together a variation on your base, trying to see how much I could minimize the need for data entry. In the process I managed to write some really ugly code that surprisingly works. I can think of a dozen problems with this base as it stands, but there are a couple of moderately clever things it does you might be able to incorporate into your work. Be warned: It could do with a thorough polishing.

As it now stands, the base contains three tables: Musicians, Groups, and Concerts.

In the Musicians table, you enter the person’s name, whether he or she is an instrumentalist or a vocalist, and a link to the Groups with which s/he performs. (The latter can be made from Musicians to Groups or vice-versa, depending on your workflow.)

In the Groups table, you enter the name of the Group and link to the Group’s members. (Or, again, vice-versa.)

Finally, in the Concerts table, you enter the name and date of the concert, the group performing, and the per-concert wages paid to instrumentalists and vocalists. (These are entered on a per-concert basis to allow for longer or more challenging concerts to be more highly paid than others.)

…and, as best I can recall, the base takes care of the rest, calculating how much each musician is paid for each concert. It also keeps track of wages paid at the individual, group, and concert level, and it tallies both per-concert and total payments to individuals and groups; it also tracks payments made each month. I’m still dissatisfied with how slight its support is at the individual level – you won’t be using this to generate individual pay stubs, for instance – and I’d love to learn of ways to do it better.

I don’t think there are too many glaring mistakes still left – but that may speak more to my lack of sleep than to my technical chops. Field naming is horribly inconsistent, and formulas are essentially undocumented – which could be a problem, considering how ferociously I took a hammer to Airtable’s screw…

If you have any questions, don’t hesitate to ask, and I’ll do my best to try and figure out just what it was I thought I was doing!

https://airtable.com/shr3tN4o3FVLmbln2


Consultant Database Design
Going mad trying to avoid duplicates with ARRAYUNIQUE
Reduce duplicates in one table to unique and count in another
#14

#15

I think that I didn’t read part of the explanation, but anyway, I think that is always better to have the Wages in an independent table, so the base is more flexible and in the future you could add more information, like a Paid field.

Also, knowing that the Wages depends on duration and on type of artist, you could calculate de Wages based on that data. Even you could have a Rates table with dates when the different rates have been valid (in force), and get the rate automctically.


#16

Thank you so much @W_Vann_Hall, this is extensive work ! I will have a look at it very shortly ! Wow !


#17

I agree. I just couldn’t figure out how to do that within the revised architecture I was fiddling around with – and eventually I looked at the clock and swore. I was mainly trying to see what I could beat out of Airtable using my current two favorite questionable hacks: Using LEN()-based math to perform calculations on multi-variable fields, as @Simon_Brown explains in this post, and packing and unpacking multiple data points into a single variable, along the lines of what I discuss here.

Now that I’ve realized this is the 21st Century, and there are plenty of clock cycles and RAM to go around – in other words, it’s OK to be… well, not sloppy, exactly; how about ‘generous’ with my code – my current obsession has been with eliminating redundant data entry and minimizing the number of tables that require manual update. My variant on your base would undoubtedly horrify Airtable product management, and I would certainly hate to have to maintain it, but in the end I was pleasantly surprised at how few fields in the base would even accept manual entry. I’m starting to think I might actually get good at this some day.

The irony is I started seeing if I could solve questions raised on the Support board as a way to hone my Airtable chops for a project of my own, only to discover recently I’ve been spending more time on other people’s issues than on my own app – mainly because I find them more interesting. (Also ironic is my interest in UI efficiency and elegance, as my personal project uses Airtable primarily as the back end to a WordPress/Airpress-driven implementation.)

Of course, if history holds, once I become easily conversant with Airtable, I’ll never have call to use it again.I am the King of the One-Off Project, both personally and professionally – so I might as well enjoy it while I can! :wink:

So maybe I will try again to see if I can’t somehow fold your separate Wages table back into the design. It’s a good idea, for all the reasons you stated. And thanks for the original base I so gleefully remixed!


#18

Hi !

I am very grateful that you put the time in for my project. This is a great way to learn from others.

I noticed that the code you use is quite advance, much more than what I am familiar with, thanks for that as well !

Have a nice day !