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

Ninox Profile

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!

 

Ninox Profile

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

Ninox Profile

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!

Reply