• Registered: 2022-01-22
  • Posts: 4

Topic: Filter combox results

Hi

I have 3 tables:
Photo (fields:id,  id_company , etc etc)
Company (fields: id, id_companytype, title)
Company type (fields: id, description)

On my photos form I want a combo box with a list of values from the company table and save the value into the Id_company FK. It also needs to allow for Null values where there is no company involved.

But I only want to display where the company-type.description = Photograpghy
The company table can contain lots of different companies of different “company types”

And for the life of me I can’t find a way to do it

Any help is greatly appreciated

Thanks smile

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Filter combox results

Hi,
One of the most straightforward ways (no script required) is to use the 'parent combobox' option (please see attachment).
This allows you to select your company type (Photography) and then filters the list of companies accordingly.
Optionally, you can also set the companytype combobox to only show 'Photography' (see screenshot in the attachment) although this is then making your form a bit less general purpose (which may be what you want).  Or you could do away with the companytype combobox altogether and use a calculated field in conjunction with the company name.
Derek.

Post's attachments

Attachment icon rcolema.zip 992.52 kb, 226 downloads since 2022-02-15 

  • Registered: 2022-01-22
  • Posts: 4

Re: Filter combox results

Thanks for this.

How would I default it to 'Photography'? Would it then be possible to make that first drop down invisible too?

Thanks

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Filter combox results

Hi,
In order to default any combobox to a specific value, you set the combobox default index to the record ID that you want as your default (see the screenshot in the attachment)  So to answer your question, yes, it can be done.
Personally, it's something I don't like because
1.  it implies the user setting up the 'company type' records, knows the ID of the one that needs to be the default index.
2.  the default index record ID may get deleted.
Have a look at the attachment as an alternative.  In it, I have
1.  added a new column to the 'companytypes' table.  This column is used to flag which of the company types is 'Photography' (I tend to use an '*' but you can use anything).
2.  use a calculated field that concatenates the company name and the field that is used to flag if the company is of type 'photography'.  If the flag is not blank (ie an '*'), the calculated field is built but if the flag IS blank, the calculated field IS NOT built
3.  use this calculated field as the combobox filter and check for it being not blank;  your combobox is populated accordingly.
Doing it this way, nothing is hard-coded and
1.  you don't need to know any index record IDs
2.  you don't have to worry that index record IDs may change
3.  you don't have to use any specific character as a 'flag'
4.  you can choose more than one 'companytype' if required (eg you may want to see 'photographers' and 'artists') by putting an indicator (eg '*') against any 'companytype' you choose, so it gives additional flexibility.
All of the above sounds way more complicated than it actually is.  Please have a look at the attachment and it should be easy enough to follow.
Any questions, just shout.
Derek.

Post's attachments

Attachment icon rcolema2.zip 1.52 mb, 241 downloads since 2022-02-15