How can I create this?

[eranggg]

Hi,

I have a table of assets, faults, professionals and suppliers. Every time a fault is over, an email is sent to the customer (automation of email sending). Each property can have several malfunctions, each malfunction can have several professionals who handled the malfunction and several suppliers through whom we bought products to handle the malfunction. In the table of professionals there is the private bank account of each professional and the same also in the table of suppliers. Is there a way that if the fault is over and 2 professionals have repaired it and we have bought materials to treat the fault from 2 suppliers, the customer will be sent an email in the following format: Hi (customer's name), please transfer payment for the fault's termination to (professional 1) to the following bank account details: Bank - xxxxx branch - xxxxx bank account number - xxxxxx In addition, please transfer payment for (professional 2) to the following bank account details: Bank - xxxxx branch - xxxxx bank account number - xxxxxx In addition, please transfer payment for (provider 1) to the following bank account details : Bank - xxxxx Branch - xxxxx Bank Account Number - xxxxxx In addition, please transfer payment for (Provider 2) to the following bank account details: Bank - xxxxx Branch - xxxxx Bank Account Number - xxxxxx Is it possible to create something like this?

[Blake_D]

If you are using the "Assets" table as a parent table and the "Faults" are linked to them with professionals and suppliers linked to the faults, you can set up the automation where when the fault is over it finds the information for those professionals and suppliers and adds the information as a list or grid within the body of the email. The amount of suppliers or professionals would not matter. I am not sure how you calculate the "amounts" for each thing so I will leave that part out of the solution.

Automation Setup:

Trigger: when record matches condition "Fault Over" in Faults table

find: professionals in professional table that match the linked field "Professionals Assigned" 

find: suppliers in suppliers table that match the linked field "Suppliers Assigned"

Action Send Email

• to "Customer Email"
• Subject "Payment Details for Fault Resolution"
• Body 

Hi (customer's name), please transfer payment for the fault's termination to the fallowing:

Professionals: (Insert professionals search results as grid with correct fields selected showing the bank information)

Suppliers: (Insert suppliers search results as grid with correct fields selected showing the bank information)

If you are going to add amounts for each person and supplier you can have a field in the faults table where you can put the professional/supplier and amount in a long text box that you can also insert into the email where needed. If you want your amount to be attached to each person automatically then a formula in the assets table could be used with lookup fields for the professional(s) and supplier(s) bank information to attach those amounts to the correct bank information and use that as the body of the email as well. 

If you have any questions please feel free to ask. 

[eranggg]

I already tried it and it didn't work. Because I need the email to be in the right-to-left direction and the table shows everything from left to right, but I also don't think I was able to produce this list.

[Blake_D]

Are you able to attach a photo just to better help me understand what you are referring to?

[eranggg]

Is it possible to add multiple supplier fees to that grid?

Currently, I can add multiple suppliers, but I can only have one supplier fee. How can I make it possible to add multiple fees? I want to use the currency column for that if possible.

Thanks.

[Blake_D]

This may add a little extra work and would add another layer to the base, but if you had a "Supply Invoice" table that is linked to the fault. Each record in the invoice table could be the supplies, supplier, fee, bank info, etc., and you could pull that linked information in your grid. So instead of looking in the linked supplier table the automation would be looking in the linked "Invoice(s)" table and adding that to the email.

[eranggg]

It will work.
but in the interface I will need to submit 2 forms to make it work.
1 for the supplier invoice and 1 for the fault.

I want to submit only 1 form.

is it possible?

[Blake_D]

Yes, it is possible. I am not sure how comfortable you are with scripting but here is a link to a quick base I put together with basic information: https://airtable.com/app1XlSMjxz4DAi6W/shrnSlIq0TUv0iP8x

Feel free to look at the script and play around with the base. You may have to copy the base to your workspace to change things but here is a screenshot and script. You could then use the "combined invoice info" in your email body.

let table = base.getTable('Faults');

let query = await table.selectRecordsAsync();

for (let record of query.records) {

    let professionals = record.getCellValue('Professionals');

    let professionalBankInfos = record.getCellValue('Professional Bank Info');

    let suppliers = record.getCellValue('Suppliers');

    let supplierBankInfos = record.getCellValue('Supplier Bank Info');

    let invoice = record.getCellValue('Invoice');

    let professionalInvoice = "";

    let supplierInvoice = "";

    if (invoice) {

        let invoiceLines = invoice.split('\n');

        if (professionals && professionalBankInfos) {

            professionals.forEach((professional, index) => {

                let professionalInvoiceLine = invoiceLines.find(line => line.startsWith(professional.name));

                if (professionalInvoiceLine) {

                    let amount = professionalInvoiceLine.split(': $')[1];

                    professionalInvoice += professional.name + ": $" + amount + " - Bank Info: " + professionalBankInfos[index] + "\n";

                }

            });

        }

        if (suppliers && supplierBankInfos) {

            suppliers.forEach((supplier, index) => {

                let supplierInvoiceLine = invoiceLines.find(line => line.startsWith(supplier.name));

                if (supplierInvoiceLine) {

                    let amount = supplierInvoiceLine.split(': $')[1];

                    supplierInvoice += supplier.name + ": $" + amount + " - Bank Info: " + supplierBankInfos[index] + "\n";

                }

            });

        }

    }

    let combinedInvoiceInfo = "Professional Invoice:\n" + professionalInvoice + "\nSupplier Invoice:\n" + supplierInvoice;

    await table.updateRecordAsync(record.id, {

        'Combined Invoice Info': combinedInvoiceInfo.trim()

    });

}

[eranggg]

I prefer not to use scripting since i dont understand that.