You are viewing limited content. For full access, please sign in.

Question

Question

SUMIF Expense column based on default value from dropdown field input

asked on June 10

I'm revamping my Expense Report.  Essentially, I'm trying to do the following:

Employees enter an expense in table rows (1 per row) and if the expense type is a "Gift" or "Entertainment" then an additional column appears with a pulldown list of client names that are populated from a database lookup.

That's the easy part.  What I want to do is have a second table that summarizes (by client name) the total amount of gifts in the first table.

 

I'm able to get SUMIF to work based on just the expense type but I can't figure out how to add the client name component.

 

use information from above to populate this table (only if "Gift" or "Entertainment")

Thanks in advance!

 

0 0

Replies

replied on June 11

Formulas do not have a way to determine unique values from inputs on the form and they do not have a way to populate more than one field (one per row in the table). It seems like you want a pivot of sorts. For this I would recommend some simple JS to populate the second table. Which form designer are you using?

0 0
replied on June 11

A pivot is exactly what I'm looking for.  I just don't know how to do it.  I don't know JS or CSS.  I am using Forms 11.

0 0
replied on June 11

Based on the file upload field it looks like you're using the new designer. Which field in the first table are you trying to summarize? What field type is that field? Checkbox converted to a dropdown? It looks like it should support multiple values but may be a single line field.

0 0
replied on June 12

I would like to show by "Client(s) present":

Sum of "Amount" if the "Type of Expense" = "Client Gift" or "Client Entertainment".

 

"Clients Present" is a dropdown list that is filled from a database lookup.

"Type of Expense" is just a dropdown field.

0 0
replied on June 12

Try this code and let me know if it works for you. You will need to replace the fieldIds in the first 6 lines of the code.

const expenseClientNameId = { fieldId: 26 };
const expenseTypeId = { fieldId: 29 };
const expenseAmountId = { fieldId: 31 };

const clientSummaryTableId = { fieldId: 27 };
const clientSummaryClientNameId = { fieldId: 28 };
const clientSummaryAmountId = { fieldId: 30 };

// Any time the expense client name changes, we need to update the client summary table
LFForm.onFieldChange(async () => {
  // get unique client names and sum amounts
  let summaryClientNameList = {};

  for (let i = 0; i < LFForm.getFieldValues(expenseClientNameId).length; i++) {
    const clientName = LFForm.getFieldValues({
      fieldId: expenseClientNameId.fieldId,
      index: i,
    });
    const amount = LFForm.getFieldValues({
      fieldId: expenseAmountId.fieldId,
      index: i,
    });
    const type = LFForm.getFieldValues({
      fieldId: expenseTypeId.fieldId,
      index: i,
    });
    let clientAmountInSummary = summaryClientNameList[clientName] ?? 0;
    if (type === 'Client Gift' || type === 'Client Entertainment') {
      clientAmountInSummary = clientAmountInSummary + amount;
    }
    summaryClientNameList[clientName] = clientAmountInSummary;
  }
  // separate the client names and amounts into two lists
  const expenseClientNameList = Object.keys(summaryClientNameList);
  const expenseAmountList = Object.values(summaryClientNameList);

  // update the summary table to have the same number of rows as unique client names
  const curRowCount = LFForm.getFieldValues(clientSummaryTableId).length;
  if (curRowCount < expenseClientNameList.length) {
    await LFForm.addRow(
      clientSummaryTableId,
      expenseClientNameList.length - curRowCount
    );
  } else if (curRowCount > expenseClientNameList.length) {
    await LFForm.deleteRow(
      clientSummaryTableId,
      ...Array.from(Array(curRowCount - expenseClientNameList.length)).map(
        (_, i) => curRowCount - i - 1
      )
    );
  }
  // update the client name fields and amounts in the summary table
  await Promise.allSettled([
    LFForm.setFieldValues(clientSummaryClientNameId, expenseClientNameList),
    LFForm.setFieldValues(clientSummaryAmountId, expenseAmountList),
  ]);
}, expenseClientNameId);
0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.