AnsweredAssumed Answered

Have field equal to all possible distinct options of a related field

Question asked by madmike6537 on Aug 31, 2015
Latest reply on Sep 14, 2015 by madmike6537

Hello,

 

I have a tough one, thinking of scripting it but figured I would ask the experts first if there is a way to do this with a calculation.

 

I have a field, call it fieldA. I would like fieldA from table A to be equal to the Distinct options of the data entries in fieldB in table B. I need the format to be in a text list inside the field like so:

 

field A Table A (all inside one field and one record)

Primary Key       Field A

1                       option1

                          option2

                          option3

 

Field B from Table B (3 records)

Record primary key   FieldB

1                               option1

2                               option2

3                               option3

 

I have tried using ExecuteSQL with something like this:

 

ExecuteSQL("SELECT DISTINCT FieldA from TABLEA"; "" &)

 

It returns the results in the right format, but there are duplicates, I need the distinct values. The other problem with this is that I need RELATED values, and of course ExecuteSQL doesnt automate that. I am not sure I can make my WHERE statement work to show the right related values. Was hoping for a calc to do this instead of using SQL maybe.

 

Any suggestions?

Outcomes