0

Update Reference field from Text Field

Hello everyone.

Assume I have 2 tables.

1. Customers -- It includes a field named "Company Name"

2. Service Reports -- It includes a popup field named "Customer" where I can select the company name from the "Customers" table.

Additionally in Service Reports, I have a text field named "Serviced Customer".

If the Serviced Customer field receives a company name (e.g. manually entered) which exists in the "Customer" table (more specific, the Company Name field, I would like the Customer field to update and show the same company name.

I have attached some screenshots as it's a bit difficult to explain.
I have no clue what code I would need to trigger the "Customer" field to find and show the customer mentioned in the "Serviced Customer" field.

Anyone willing to give me a code example for this?

4 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Why do you type in the name of the company into Serviced Customer when you already have it stored in the Customer table? Can't you drop the Serviced Customer field and just use Customer?

    To give you a possible answer to your question, you can create the link to Customer when changes are made to the Serviced Customer field. You would use the Trigger after Update on the Serviced Customer field and the code would look something like:

    let t := this;
Customer := first(select Customer['Company Name' = t.'Serviced Customer'])
    • NetSol Co., Ltd.
    • Bernd_Krell
    • 2 yrs ago
    • Reported - view

    Hello Fred 

    Thanks a lot. Well this was only an example as the real workflow I need the code for is a bit more complex.
    I have an external Form (Cognito Forms) which our field staff uses to submit their service reports. I then use Zapier as the connector between Cognito Forms and Ninox. All information submitted through this form come in to Ninox just fine with the exception of the Customer's Company Name.
    So I created that text field named "Serviced Customer" and Zapier puts in the company name fine. Therefore I wanted a code which then selects the same company name from the reference field so the service report is eventually linked to the customer which is important for billing.

     

    I tried your code but it doesn't work. See screenshot. Maybe because "Customer" is a reference field?
     

    • John_Halls
    • 2 yrs ago
    • Reported - view

    According to your data model screenshot, Customer should be Customers

    let t := this;
Customer := first(select Customers['Company Name' = t.'Serviced Customer'])

    Regards John

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 2 yrs ago
    • Reported - view

    Oh my god, such a small thing. Thank you so much John Halls to point out the missing "s".

    Also thanks to Fred
    Everything working now as expected.

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 4Replies
  • 97Views
  • 3 Following