Skip to main content

Help with multi selection and using in a function

  • March 29, 2018
  • 1 reply
  • 10 views
M
Forum|alt.badge.img+1

As I make a sale, I have a field in a linked table called “Contract Activity” that contains a multi-select field containing information on when the service and billing will be done by month/year, (Jan 2018, Feb 2018 and so on). I have a field called “Monthly Total” that contains the amount to be billed (formula field that multiples “Sessions per month” by “Hourly Amount”). I need to create a table that will break down the ‘Sales by Month’.

I want all the records from my “Contract Activity” table to be on my Monthly Sales/Payments table (can filter from there) with my columns being each month as my original table doesn’t configure these monthly services/billing amount but I can’t get the formulas to work. I have tried nested IF function to account for the multi-select field but my results are only applied to the records with only one payment date selected (and the records will not rollup in my New table). I don’t want to do this in the original table as I will need to add other fields to the new table that I don’t want in my “Contract Activity” table.

If Function:
IF({Months of Service}= “Jan 2018”, “January,”, IF({Months of Service}= “Feb 2018”, “February,”,IF({Months of Service}= “Mar 2018”, "March,"IF({Months of Service}= “Apr 2018”, “April,”,IF({Months of Service}= “May 2018”, “May,”,IF({Months of Service}= “Jun 2018”, “June,”,IF({Months of Service}= “Jul 2018”, “July,”,IF({Months of Service}= “Aug 2018”, “Aug,”,IF({Months of Service}= “Sep 2018”, “September,”,IF({Months of Service}= “Oct 2018”, “October,”,IF({Months of Service}= “Nov 2018”, “November,”,IF({Months of Service}= “Dec 2018”, “December,”,IF({Months of Service}= “Jan 2019”, “January,”,IF({Months of Service}= “Feb 2019”, “February,”,IF({Months of Service}= “Mar 2019”, “March,”,IF({Months of Service}= “Apr 2019”, “April,”,IF({Months of Service}= “May 2019”, “May,”,IF({Months of Service}= “Jun 2019”, “June,”,IF({Months of Service}= “Jul 2019”, “July,”,IF({Months of Service}= “Aug 2019”, “Aug,”,IF({Months of Service}= “Sep 2019”, “September,”,IF({Months of Service}= “Oct 2019”, “October,”,IF({Months of Service}= “Nov 2019”, “November,”,IF({Months of Service}= “Dec 2019”, “December,”,)))))))))))))))))))))))).

Link to database view:
https://1drv.ms/u/s!AmzvdHmUMXTOhgCisVS6-Zf4bIvx

I would really appreciate if anyone has made this work with Multi-selection fields and has used them to create this type of function. If anyone can help as I am very frustrated on why this will not work as it’s so easy in Excel.

Thank you,
Mary

    1 reply

    W
    Forum|alt.badge.img+5
    • W_Vann_Hall
    • Inspiring
    • April 4, 2018

    That seems like an incredibly masochistic way of trying to accomplish your goal. :winking_face:

    Without diving too deeply into your base, I would recommend defining a single-select for {Month of Service} listing 'January'…'December', a single-select for {Year of Service} with options '2018' and '2019', and concatenating them together in a third field with {Month of Service}&' '&{Year of Service} . Then, I’d steal some of the routines from my Sales CRM Dashboard base from Airtable Universe to break out the per-month totals.

    Unfortunately, at the moment Airtable is going to make you break out your monthly totals explicitly, no matter what. With your approach, you also have to assemble them explicitly on the input side, as well, where with mine you can input a month and year and let the base figure out in what bin to dump the result.

    Terms & ConditionsAccessibility statement