Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Conditional Value List: Please Tell Me I Can Do This...


tangledcords
 Share

Recommended Posts

So, I'm new to FM, and for the most really like the tool. However, I've run into a problem that, so far, I've not been able to provide a solution for and I am really hoping it's a user error rather than a limitation of the tool. I'm guessing it's the former rather than the latter.

 

So, I am creating appointments for car detailing. One of the attributes of an appointment are the service the customer requested. I also have multiple locations which means each location has their own set of services. I'll outline the relevant parts of my data model below:

 

Appointments (AppointmentID, ServiceID)

Location (LocationID, locationDescription)

Services (ServiceID, ServiceDescription)

 

I have Services joined to Appointments via serviceID and Location joined to Appointments via locationID. On my Appointments layout I have two drop-down lists for location and service (both of which are using the ID's for their respective fields).

 

I also have setup Value lists for Location as such:

Use values from field: "Location::locationID" and "Location::locationDescription" where I'm using the value locationID as the first field and locationDescription as the second. The value list content is set to "Include All Values".

 

Then, my Services Value list is set up like this:

Use values from field: "Services::serviceID and "Services::serviceDescription" where I'm using the value serviceID as the first field and serviceDescription as the second. The value list content is set to include only related values starting from Location.

 

The problem has been that I never get a complete and accurate list of services for any location. No matter what I choose for location, the services list is wrong, and at times empty when there are clearly services defined for the location.

 

If I remove the Appointments table from the mix, and join Location to Services directly everything works. So, is it possible my data model is incorrect? I just can't imagine this should be so hard?

 

I am so frustrated right now and hope I provided enough information. If I can't get this working in this context I will unfortunately have no choice but to abandon FM all together, which would be a shame, b/c I really like most of the product.

 

I am sorry to be so long, but I have read through so many posts and at this point I have no other options. Please advise...

Link to comment
Share on other sites

Appointments (appointmentID, serviceID)

Location (locationID, locationDescription)

Services (serviceID, serviceDescription)

 

Sorry, I left out a relevant field from Appointments. It should read:

Appointments (appointmentID, serviceID, locationID)

Link to comment
Share on other sites

You need to place another TO (table occurence) of the Services table on the relationships graph (let's call it AvailableServices) and link it to Locations:

Locations::LocationID = Services::LocationsAvailable

 

The field LocationsAvailable in Services should be a checkbox field, showing all values from the field Locations::LocationID.

 

Then use a new value list showing values from the field AvailableServices::ServiceID (only related values starting from Appointments) to select the service.

 

You can also switch things around, so that instead of specifyng at which locations a particular service is available, you would have the checkbox in Locations, specifying which services are available at a particular location. It's merely a matter of convenience.

 

Unrelated to your question, I am bothered by your having a Services (plural) field in Appointments. If an Appointment can have multiple services, you should use a related join table to specify them - just like line items on an invoice.

Link to comment
Share on other sites

Thanks for your reply. First, let me start at the end. I am glad for your recommendation. I had started trying to change this over a few days back but wasn't sure if I was headed down the right path, but you have confirmed that I am.

 

So, here is my current schema (only relevant fields included):

Appointments (appointmentID, serviceID, locationID)

Location (locationID, locationDescription)

Services (serviceID, serviceDescription)

 

Here is my proposed schema:

Appointments (appointmentID, locationID)

Location (locationID, locationDescription)

LineItem (lineItemID, appointmentID, serviceID)

Services (serviceID, serviceDescription)

 

where Location and LineItem are joined to Appointments and Services is joined to LineItem. Does this make better sense to you? If so, how would that change your answer to my original question?

Link to comment
Share on other sites

You still need a relationship between Locations and another occurences of Services, and a field to support this relationship.

Link to comment
Share on other sites

Wouldn't that require serviceID be in the Location table then? Or am I just not following what you are saying?

Link to comment
Share on other sites

In addition to ServiceID in LineItems, you need either a ServiceIDs (plural) in Locations, or LocationIDs in Services. This field needs to be text, and formatted as a checkbox, so you can assign services to a location, or vice-versa.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use