0

How can I select a record based on a value in a multiple choice field?

Hello all,

I need some help selecting one record in a table based on a value in a multiple choice field in the record. Here is the setup:

Table: Client_Address

Contains one or more addresses for a client

Contains a multiple choice field for the type of address (Home, Site, Billing, Other)

Contains a Y/N field to indicate if the address is to be used (for a site address in invoice)

M:1 relationship with Client table

Table: Invoice

Needs to have a Billing Address and a Site Address (which could be the billing address)

Issue #1

I have a function field for Billing address to concatenate the Street + City + State + Zip. As expected this field currently shows an array of all Client_Address records for that client.

I have been playing with variations of the following code to return only the record where “Billing” is included in the multiple choice field:

select Client_Address where contains(text(chosen('Address Type')), “Billing”);

Client.Client_Address.Street

I want to return only the record where ‘Address Type’ = “Billing”, however this code is still returning all options. I’m sure I’m missing a big of syntax or code, but I have not been able to locate the error.

I have tried to add additional boundaries on the Array (such as first(), last(), etc), but they do not give me the record where the condition is true.

 

Issue #2

I have a view to the Client_Address table on the Invoice form so I can up date the Y/N flag to yes if I want to use that address for the Site Address in the Invoice. This is currently triggered by a button that will also set the Y/N flag to no once the Site Address is updated on the Invoice.

There will only ever be one Site Address per Invoice.

This is the code on the button:

SiteAddress := first((select Client_Address)['Use Address' = "Yes"].Street);

(select Client_Address).('Use Address' := "No")

Setting the Y/N flag to “No” does work, but the SiteAddress field is not updated.

Any help would be appreciated!

2 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    Let's start with the first issue and move on to the second once you've got the first one working. This code goes in a formula field and displays the address that matches the "Billing" criteria...

     

    "// Declare a variable to hold a line feed (alt+enter)";
    let lf := "
    ";
    "// Get the currently selected record Id in the Client table";
    let tId := Id;
    "// Select the matching address in Client_Address table";
    (select Client_Address where Client = tId and contains(concat(chosen(Type)), "Billing")).(Street + lf + City + ", " + State + " " + Zip)

     

    You can copy and paste the code into a formula field in your Client table

    • info.17
    • 4 yrs ago
    • Reported - view

    This worked perfectly!! I've also been able to make my button work to update the site address on the invoice:

    let c := Client.Id;
    SiteAddress := first(select Client_Address where Clients = c and 'Use Address').Street;
    'Site City' := first(select Client_Address where Clients = c and 'Use Address').City;
    'Site State' := first(select Client_Address where Clients = c and 'Use Address').State;
    'Site Zip' := first(select Client_Address where Clients = c and 'Use Address').Zip;
    Client.Address.('Use Address' := "No")

    Thanks so much for your assistance Sean!

Login to reply

Content aside

  • 4 yrs agoLast active
  • 2Replies
  • 3869Views