Dan Zahlis replied 2 years ago

Thanks, Bobby. I don't have any problem getting lookup fields to display. I'm trying to have four levels of filtering lookup fields result in a "view field" from a source table (Site Services). I do not want the user to have to select an option from the last lookup field, in this case "Waitlist?".

The use case is as follows: The client offers mental health services to school sites. Client schools enter Referrals for mental health services into the Referral form. The therapists are periodically booked (unfortunately), and the school has to be placed on a waiting list. We need to display a message regarding the waitlist status to the person entering the referral.

I want to add a "Waitlist?" field (toggle or radio) to the Site Services form so the therapist administrators can place a school site on a waiting list. When the school site enters a new referral: District -> School Site -> Service Type -> Service -> Waitlist? (a series of filtered lookup fields from the Site Services form), I want the last field (Waitlist?) to populate as a "view field" (not a selectable field) that displays the "Waitlist?" field value from the matching Site Services record. I want to use the "Waitlist?" field to trigger an HTML message to the person entering the record regarding the waitlist status (using conditional logic - when "Waitlist?" = "Yes").

Is this even possible?

Dan Zahlis replied 2 years ago

One of the things that causes my brain to malfunction is that I'm accustomed to working with a relational database where I can associate record IDs and then display related field data. That's basically what I'm trying to do with this "Waitlist?" field. It would be cool if the Entry ID or Entry Key could be saved along with a "View Field" when a unique combination of lookup fields reveal the associated ID/Key. Just thinking out loud...

Bobby Clapp replied 2 years ago

I think so. It's been a while since I've needed something like this so this may not be the best route today. This is what I used to do.

Tiered forms/entries:
The first form would be a field "District". All districts entered.
The second form would be a lookup of "District" with a field for "Service Type". All service types added to each appropriate district.
The third would be a lookup of "District" and a lookup of "Service Type" set to watch the district field. And finally a Service field. As before add the appropriate service to the service types.
The fourth form would have a lookup of "District", a lookup of "Service Type", and a lookup of "Service" all watching the previous. Finally, you would have a "Waitlist" field. Add whatever has a waitlist.

Now you can add lookup fields from the fourth form to the form in question, each watching the previous. And based on the way the data is entered in this way, it would only be able to capture the data entered through each process, tying it all together.

If it works and you add a HTML field to the form with your message and if it can watch the waitlist field and then have conditional logic applied that says if "waitlist" equals "yes", it should work.

Give it a shot and see if it still works.

Bobby Clapp replied 2 years ago

You can do some interesting things with parameters as well. It might be doable that way too. I just can't explore all that for free. Even then, I can't guarantee I have a solution. Good luck!

Dan Zahlis replied 2 years ago

Thanks again, Bobby. I have built the entire system using "tiered forms/entries" as you suggest. I was trying to recreate a relational database schema so the therapists would only have one place to change a given piece of data (little did I know...).

I just tried something that didn't work but theoretically it would be cool if it did. I put the short code [frm-field-value field_id=370 show=value] in the Advanced->Default Value field of the "Waitlist?" field. I was hoping it would auto display the saved field data from the School Services form. I tried the Key as well. No dice.

Thanks for your time. I ran an open source project for a decade and I'm currently volunteering my time with this FF site to help my daughter's NFP. I understand the limitations of free!