Search Workflow Help

[zacharyreed]

Hey guys! I have a question about searching and combining records. I have the following Database structure with fields listed:

Search

• Available Policies (linked to Policy)
• Conditions (list of linked Condition)
• Prescriptions (list of linked Prescription)

Policy

• Name
• Insurance Carrier (linked to Insurance Carrier)
• Declined Conditions (list of linked to Policy Condition)
• Description

Policy Condition

• Condition (linked to condition)
• Prescription (linked to prescription)
• Allow or Decline (“Allow” or “Decline”)
• Policy (linked to Policy)

Insurance Carrier

• Name

Condition

• Name

Prescription

• Name

My app is allowing users to fill out a form and select different conditions. On the last step, I am attempting to generate a list of policies that are allowed by removing all policy options that are Declined from their selected Condition & Prescription list on the “Search” record. Each Policy has a set of allowed or Declined “conditions” or “Policy Condition: records.

The search process is stepped, so each step adds the field values into the “Search” record. I have everything figured out except this last step for finding available policies based on the “Condition” & “Prescription” added to the search record.

Any recommendations for compiling a formula/workflow when the “find available policies” button is clicked to actually find the correct policy based on the searching requirements I need?

[cmarchan]

This may help

SQL Joins Vs Bubble

Thought this might help, if you aren’t coming from an SQL background then it will probably confuse! Two Tables, not linked (we can do that another day). Let’s not think about duplicate values either. Table A Name Parrot, Banana, Monkey, Apple Table B Name Ruby, Banana, Diamond, Apple INNER JOIN SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name [image] In Bubble this is achieved via an INTERSECT of two searches. [image] [image]

[zacharyreed]

Here is a visual of the tables and how they would “inner link”. Need to add available policies in a list for search record, checking to make sure the conditions & prescriptions are “allowed” from the policy conditions and therefore add that policy to the available policies list on the search record.

[NigelG]

I think you have the data pretty much spot on, although I am not sure what the Search thing is for. It may make the search saveable/easier to manage I guess?

So my suggestion is that the search does …

Search for Policies where the conditions are allowed. So get this bit working first.

It will probably be an Advanced Filter, so where “this condition” (the list they have selected - maybe on a custom state) is “allowed” and “is in” list.

Then get a search where the opposite is true so “this condition” is “Decline”.

Once you have them both working on a test page…

Do a :minus list of the second list on the first.

[zacharyreed]

Thanks @NigelG ! I followed a very similar path with the policy conditions and comparing them to the selected conditions that then removes the “Declined” Policy Conditions and was able to get it to work perfectly!