Cannot get a Virtual Field to filter results correctly

[Jacob1701]

Hello

I have a datapage that shows appointments that patients have had.  The data source is the Appointments table and has the patients MemberID in that table.

I want to make a Virtual Field in the search configuration page to narrow the results by patients with certain employers but I cannot get it to work correctly.

In the results page, I do have the employer shown correctly but I cannot make a search field for this.

What can I do in the Virtual field to narrow down the results by employer?  Employer is not in the Appointments table. only memberID.

I would like the Virtual Field to be an autocomplete or dropwown.

 

Any suggestions?

 

Thanks

[AnchovyXL]

Hi,

If I have understood you correctly, you are trying to filter the results page by employer.

Since the Employer Field  is not included in the Appointment table, I suggest your create a view that combines the Appointment table and the Table that has the Employer Field on it.

You should have a field that links the two tables in order to accomplish this.

After that, use the view as the Data Source of your report.

I hope this helps.

Thanks!

[Jacob1701]

Yes that was exactly my problem.  I already had a member/appointment view for a different data page so I added Employers to it and used that as the data source and it is working.  

However, I found that on to of my calculated fields, I had to add target.

For example, the original was 

SELECT Employer_name FROM _v_Member_Appointment WHERE Appointments_MemberID = [@field:MemberID] in the results field (which I do not need anymore due to the view)

but I found that the new data page with the view as the data source I had to add target like this

SELECT Employer_name FROM _v_Member_Appointment WHERE Appointments_MemberID = target.[@field:member_MemberID]

Later I realized I did not need those calculations in the results page because I now have the ability to select Employer due to the view but it just got me wondering what does the target. used for?

I do have some of my search fields looking up the actual tables and not the view.  Would this have anything to do with it?

Otherwise everything is working now.

 

Thanks