Filemaker Value list does not populate data from multiple tables
Trying to create a value list that displays only active products being used by a patient.
We have our patients table, medications table, products table.
The medications table house all current and previous medications that patient was using, such as product ID, contact ID, active or inactive status, and other miscellaneous information.
The products table houses all the information for a specific product, such as product ID, description, brand name, dosage strength, etc.
The patients table houses data such as, patient ID, contact ID, active status, etc.
On the patients layout, when products are added to a patient either it be an Rx or Ancillary product it would be placed in in a portal called Patients_Medications_Rx or Patients_medications_Ancil
That would display the history of products that patient has used and is using. The same time there is a field that the user selects as if it is active or an inactive product.
When our user goes to make a delivery form from patients layout, the user would select from the Rx or Ancillary portion of the delivery form the products that patient is using. The problem is that it displays all the products including the "inactive" products.
I was trying to creat a value list that would only display the "Active" products per specific category of products such as "Ancillary, OTC, Rx, etc.
Assistance in this would be greatly appreciated.
We have our patients table, medications table, products table.
The medications table house all current and previous medications that patient was using, such as product ID, contact ID, active or inactive status, and other miscellaneous information.
The products table houses all the information for a specific product, such as product ID, description, brand name, dosage strength, etc.
The patients table houses data such as, patient ID, contact ID, active status, etc.
On the patients layout, when products are added to a patient either it be an Rx or Ancillary product it would be placed in in a portal called Patients_Medications_Rx or Patients_medications_Ancil
That would display the history of products that patient has used and is using. The same time there is a field that the user selects as if it is active or an inactive product.
When our user goes to make a delivery form from patients layout, the user would select from the Rx or Ancillary portion of the delivery form the products that patient is using. The problem is that it displays all the products including the "inactive" products.
I was trying to creat a value list that would only display the "Active" products per specific category of products such as "Ancillary, OTC, Rx, etc.
Assistance in this would be greatly appreciated.
ASKER
Will, I am going to try the first option to see if it works.
ASKER
Will, I cant get it to work. It either displays all our products or none at all.
I went from Patients(Contact_ID) to Medications(ContactID).
Then Medications(ActiveRX(holds
In valuelist I selected to use productID and product combo name to display.
If I leave as that, it displays all the products.
If I change it to "start from = Medication", I get no values.
On our delivery form, it must display the product name and keep the productID in the Delivery Table, not the name.
I went from Patients(Contact_ID) to Medications(ContactID).
Then Medications(ActiveRX(holds
In valuelist I selected to use productID and product combo name to display.
If I leave as that, it displays all the products.
If I change it to "start from = Medication", I get no values.
On our delivery form, it must display the product name and keep the productID in the Delivery Table, not the name.
Start from Patient and set the Value list to display two calculation fields from Mediations:
ProductID_Active Case( not is empty( Active ) ; Medications::ProductID )
ProductName_Active Case( not is empty( Active ) ; Product::ProductName )
I think this will work. The issue you might run up against is that if you pull both ID and Product values from the Products field then you are using fields that are not directly indexed in Medications. ProductID IS or should be indexed in Medications.
PatientOrder.fmp12
ProductID_Active Case( not is empty( Active ) ; Medications::ProductID )
ProductName_Active Case( not is empty( Active ) ; Product::ProductName )
I think this will work. The issue you might run up against is that if you pull both ID and Product values from the Products field then you are using fields that are not directly indexed in Medications. ProductID IS or should be indexed in Medications.
PatientOrder.fmp12
ASKER
Will,
Those two fields are created in Medications, correct? Which I did.
I did hit an error when trying to create the second one: ProductName_Active Case( not is empty( Active ) ; Product::ProductName )
I get an error message: "The field comes from an unrelated table. only global fields can be referenced in unrelated tables."
Those two fields are created in Medications, correct? Which I did.
I did hit an error when trying to create the second one: ProductName_Active Case( not is empty( Active ) ; Product::ProductName )
I get an error message: "The field comes from an unrelated table. only global fields can be referenced in unrelated tables."
ASKER
Will, I am stump.
I've created value list in the past, I have several of them. But for some strange reason, this does not want to work.
I need to stamp the productID but reveal the product name(is a comboname field).
Strange that it does not want to work. Frustrating that something that normally takes me 30 minutes has taken me over four hours.
Depressing is what it is.
I've created value list in the past, I have several of them. But for some strange reason, this does not want to work.
I need to stamp the productID but reveal the product name(is a comboname field).
Strange that it does not want to work. Frustrating that something that normally takes me 30 minutes has taken me over four hours.
Depressing is what it is.
Did you look at the example file I included? It has a working example.
The error message you are getting indicates that either you do not have a relationship from Medications to Products based on Medication::ProductID = Products::ProductID or the related Table Occurrence for the Products table in that relationship is not called "Products". Take a look at the table structure in the example file. The names I used may not exactly match yours...
The error message you are getting indicates that either you do not have a relationship from Medications to Products based on Medication::ProductID = Products::ProductID or the related Table Occurrence for the Products table in that relationship is not called "Products". Take a look at the table structure in the example file. The names I used may not exactly match yours...
ASKER
Will, I have been unsuccessful.
I am going bonkers that this does not want to function correctly.
I am going bonkers that this does not want to function correctly.
ASKER
Here is an image of the relationship.
I looked at your example and compared to mine and it is somewhat similar.
The difference is that my filtered picks are in a portal.
patientproducts1.JPG
I looked at your example and compared to mine and it is somewhat similar.
The difference is that my filtered picks are in a portal.
patientproducts1.JPG
Well, it's hard to tell what the problem is without seeing the layout. I doubt it will affect things but I would simply make the relationship ProductID to Product ID because you're still going to want to pull through the product name for Inactive medications. You can limit the display of the Medication to Active only in Medications.
ASKER
Will, this image is of the layout where the user, clicks in the empty field to select a product(which is suppose to display active products only but displays the patients all products that are Rx type).
Under description, the user clicks there and a pull down menu displays the contents related to Rx.
The description has two fields associated with it(one that stamps the product ID unto the delivery form table). then the field on top of that one is the ProductComboName from the Products table. This method worked for a good time, but since we are expanding and getting busy, users prefer only active items to display.
patientorder3.JPG
Under description, the user clicks there and a pull down menu displays the contents related to Rx.
The description has two fields associated with it(one that stamps the product ID unto the delivery form table). then the field on top of that one is the ProductComboName from the Products table. This method worked for a good time, but since we are expanding and getting busy, users prefer only active items to display.
patientorder3.JPG
ASKER
This image is of the value list.
It's matched to yours but all I get when I click on the layout: "No values defined".
patientorder4.JPG
It's matched to yours but all I get when I click on the layout: "No values defined".
patientorder4.JPG
ASKER
Hello Will. After a good amount of tinkering around with this issue.
I finally got it to display only the items that are active.
vide later the steps I used to resolve it.
Thank you yes for your assistance.
I finally got it to display only the items that are active.
vide later the steps I used to resolve it.
Thank you yes for your assistance.
ASKER
Will, I noticed that I left out something from my original posting compared to your sample file you sent me.
Your sample file has medications and products on the "PAtient" table.
The pop-up menu must be placed in a layout called "Patient_Deliveries"(Deliv
Your assistance would be greatly appreciated.
I can not get those to display the active type products.
Your sample file has medications and products on the "PAtient" table.
The pop-up menu must be placed in a layout called "Patient_Deliveries"(Deliv
Your assistance would be greatly appreciated.
I can not get those to display the active type products.
I would guess from your description that the Deliveries table also has a related Line Items table which contains each of the items to be delivered. Please take the PatientOrder.fmp12 example file that I created and add the additional tables and structure so that I can see it. It will be easier than guessing and going back and forth. Once that's there I can see about what's required. My guess is that you need to establish relationships from the PatientID in Deliveries similar to the ones I created from Patients and then base your value list on those relationships.
ASKER
Hi Will, I will have to come back to this case in 2-3 weeks. right now we are moving from one office space to another and I leave on holiday next week wednesday.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
I'm guessing that you're trying to create the value list in order to have either a checkbox field or series of popup menus to select the medications, but another way to do it might be to open a Popover object with a portal in it that includes filter to only show "Active" items. Add a field to Medications called "CurrentOrder" and clear it for all Medication records related to that a Patient just prior to displaying the Popover. Then have the user tick a checkbox (with a Value List of just "1") for the CurrentOrder field for whichever items they want. When they close the popover you will now have a list of their medications that they want to order because there will be a value of "1" in the CurrentOrder field for each one that they checked.