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!