Filemaker 15 How to display multiple calculated fields on a layout
I have a layout that is able to display a field that is defined as a calculation in its table. The data displays correctly. I then decided to add three more fields on my layout using three more fields that were defined as calculations. When navigating through the records, i notice that only one of the four fields on my layout displays the data that is calculated on the table. It only displays one of the four, not two or three or four, but just any one of the four.
Table A (User Settings) is in relationship with Table B (Device Settings).
The fields in the relationship are two:
ContactID = ContactID
UserLocation = DeviceLocation
All fields are entered in DeviceSettings table.
There is a dropdown on devices that is used to select the employee that is assigned that device.
That device is also entered with a value of either for field DeviceLocation: Local, Remote, etc.
The layout called User Settings is a layout that will house all the devices assigned to a user.
If an employee is at the local site as there work location, the relationship should display all items associated with that employee contact ID and place them in there corresponding field.
I used in the UserSettings table on the four fields the field type as : calculation.
I used the If structure to calculate the field. It works correctly when only one field is on the layout with the calculation field, but the moment i enter other calculated fields on the same layout, it only displays one of the four fields.
With the IF structure, i enter the condition if the field has a value of "computer", give me the deviceID(this is naming structure we use on our devices), if not enter the value "Not assigned"(this way on the user settings layout, it enters the value "not assigned" on a device that it is not assigned to an employee. That is what I see on three of the four fields. ONly one field will have a value. this ranges between employee records in usersettings. what can i do to get this working correctly?
Table A (User Settings) is in relationship with Table B (Device Settings).
The fields in the relationship are two:
ContactID = ContactID
UserLocation = DeviceLocation
All fields are entered in DeviceSettings table.
There is a dropdown on devices that is used to select the employee that is assigned that device.
That device is also entered with a value of either for field DeviceLocation: Local, Remote, etc.
The layout called User Settings is a layout that will house all the devices assigned to a user.
If an employee is at the local site as there work location, the relationship should display all items associated with that employee contact ID and place them in there corresponding field.
I used in the UserSettings table on the four fields the field type as : calculation.
I used the If structure to calculate the field. It works correctly when only one field is on the layout with the calculation field, but the moment i enter other calculated fields on the same layout, it only displays one of the four fields.
With the IF structure, i enter the condition if the field has a value of "computer", give me the deviceID(this is naming structure we use on our devices), if not enter the value "Not assigned"(this way on the user settings layout, it enters the value "not assigned" on a device that it is not assigned to an employee. That is what I see on three of the four fields. ONly one field will have a value. this ranges between employee records in usersettings. what can i do to get this working correctly?
Will Loving
Hi J, While you've done an admirable job of describing your structure and problem, my guess is that the problem lies with the calculations and what is being referenced in those calculations. We could go back and forth trying to troubleshoot this in words but I'm guessing it would take only a minute or two to figure it out if I can see the actual file. If you want to send me a copy (populated with fake data if you wish), you can do that by messaging me directly rather than posting it here publicly. Will
ASKER
Will, nice to hear from you. Our DB is about 200GBs with many tables. I can attach snippets of the relationships and tables and calculations. Will that be ok?
ASKER
Here are four pictures of my DB.
The relationship between user settings and device settings is so that it loads up the devices that are in the "main" location with matching "contactID".
In the device settings layout, when equipment is assigned to an employee it assigned as per four different locations: main, home, 34dr, NYRx.
Then on the usersettings layout, if I was to look up a user working at a specific location such as main or home, it will populate the fields for me.
this layout is a work in progress. there will be more to it.
currently information on our equipment and employees is on paper and spreadsheets and it is difficult to be going though that stuff frequently.
I rather have that at a click of a button to help me and my iT staff.
I know the calculation works because if I only have one calculation on a table to the layout it displays it on all records of usersettings.
but the moment I add another calculation in the usersettings, it displays that info on some records and others not. some records would have only one field displaying the calculated data, and othe records displaying a different field with the calculated data.
pic_layout_devicesttings.JPG
pic_layout_usersettings.JPG
pic_relationshipview_usersettings_de.JPG
pic_usersettings_fieldsviewInTable.JPG
The relationship between user settings and device settings is so that it loads up the devices that are in the "main" location with matching "contactID".
In the device settings layout, when equipment is assigned to an employee it assigned as per four different locations: main, home, 34dr, NYRx.
Then on the usersettings layout, if I was to look up a user working at a specific location such as main or home, it will populate the fields for me.
this layout is a work in progress. there will be more to it.
currently information on our equipment and employees is on paper and spreadsheets and it is difficult to be going though that stuff frequently.
I rather have that at a click of a button to help me and my iT staff.
I know the calculation works because if I only have one calculation on a table to the layout it displays it on all records of usersettings.
but the moment I add another calculation in the usersettings, it displays that info on some records and others not. some records would have only one field displaying the calculated data, and othe records displaying a different field with the calculated data.
pic_layout_devicesttings.JPG
pic_layout_usersettings.JPG
pic_relationshipview_usersettings_de.JPG
pic_usersettings_fieldsviewInTable.JPG
I'm looking at your screen shots and contemplating.... Just to make sure the basics are there, in each calculation, have you UNchecked the checkbox that says:
If that is checked, then "Not Assigned" may not appear...
Also, I note that you have the additional predicate in the relationship of "UserLocation". Is it possible that each of the records you are looking at has a different UserLocation, so the the only calculations showing results are the ones where the UserLocation in the UserSettings table matches with the specific Device?
If that is checked, then "Not Assigned" may not appear...Also, I note that you have the additional predicate in the relationship of "UserLocation". Is it possible that each of the records you are looking at has a different UserLocation, so the the only calculations showing results are the ones where the UserLocation in the UserSettings table matches with the specific Device?
ASKER
Will, good morning.
'the do not evaluate checkbox is unchecked, i want it tostamp the field with that value if it does not have if such a device at the main location does not have a device. they may have one at home but the relationship is to only select items labeled as "home'.
will write back shortly. going to have breakfast with family.
'the do not evaluate checkbox is unchecked, i want it tostamp the field with that value if it does not have if such a device at the main location does not have a device. they may have one at home but the relationship is to only select items labeled as "home'.
will write back shortly. going to have breakfast with family.
ASKER
Good morning Will. I looked over the code and fields. I unchecked the option for: "do not evaluate if all..", still works the same.
No improvement.
I looked over all the records with the value of "main', all those get the value of "main" via a drop-down list on device settings. this ensures all data values are consistent.
To test out the record on a layout, I changed the device location to "home" and then one of the fields in the user settings changed to displaya a different device assigned as "main" to the user and device location. and the original device that displayed the deviceID changed to "Not Assigned". I then changed another field to "home" and then another field displayed the deviceID.
Not sure what is going on here.
It is perplexing.
Please advice.
No improvement.
I looked over all the records with the value of "main', all those get the value of "main" via a drop-down list on device settings. this ensures all data values are consistent.
To test out the record on a layout, I changed the device location to "home" and then one of the fields in the user settings changed to displaya a different device assigned as "main" to the user and device location. and the original device that displayed the deviceID changed to "Not Assigned". I then changed another field to "home" and then another field displayed the deviceID.
Not sure what is going on here.
It is perplexing.
Please advice.
Your calculations in UserSettings are referencing multiple different relationships, not just the one you mentioned first:
NAD_UserSettings_DeviceSet
NAD_UserSettings_DS_Monito
NAD_UserSettings_DS_Phone
I can't really tell what's going on with those other relationships but if they are also using UserLocation, and that's the field that causes everything to change when you edit from "home" to "main" then I would start there.
Also, note that when you reference a field in a related table in a calculation as you have, if there are more than one related record, the value that's going to be used by the calculation is the first related record. So if there are multiple records in NAD_UserSettings_DeviceSet
Either way, if you are changing the basis of the relationship, by changing on of the keys - in this case "UserLocation" - then the records in the DeviceSettings table that relate will also change. Understand?
NAD_UserSettings_DeviceSet
NAD_UserSettings_DS_Monito
NAD_UserSettings_DS_Phone
I can't really tell what's going on with those other relationships but if they are also using UserLocation, and that's the field that causes everything to change when you edit from "home" to "main" then I would start there.
Also, note that when you reference a field in a related table in a calculation as you have, if there are more than one related record, the value that's going to be used by the calculation is the first related record. So if there are multiple records in NAD_UserSettings_DeviceSet
Either way, if you are changing the basis of the relationship, by changing on of the keys - in this case "UserLocation" - then the records in the DeviceSettings table that relate will also change. Understand?
ASKER
Will, here is a closeup of the relationship.
the relationship will pull/reference records only when contactID and UserLocation is equal to deviceLocation.
relationshipFM.JPG
the relationship will pull/reference records only when contactID and UserLocation is equal to deviceLocation.
relationshipFM.JPG
ASKER
Here is an image of user settings calculation.
Using the relationship of userSettings=deviceSetting
Using the relationship of userSettings=deviceSetting
ASKER
Will, my goal is to have my userSettings layout, display in one location (User Settings Layout) the device ID for each device assigned to the specific employee looking at in the usersettings layout. I am hoping to be able to pull this off without the use of a portal. Is a portal the only way I can pull this off?
Probably much simpler but then I will be forced to keep it all clustered together when I was hoping to spread them out.
Probably much simpler but then I will be forced to keep it all clustered together when I was hoping to spread them out.
I got the relationship diagram which is the same as before I think but there was no image attached to the second of three posts.
My point was that your calculations are not referencing the same relationship. Two of them are and two aren't. If there are multiple Device settings records that are related via ContactID and Location, your calculation is ONLY going to show the result of the first related records, unless you use something like the List() function to obtain multiple values or some other method to select the specific value you want to see.
My point was that your calculations are not referencing the same relationship. Two of them are and two aren't. If there are multiple Device settings records that are related via ContactID and Location, your calculation is ONLY going to show the result of the first related records, unless you use something like the List() function to obtain multiple values or some other method to select the specific value you want to see.
ASKER
Will, the List() function?
Do I use that on the field as calculation and work it as using similarity to case() function?
Do I use that on the field as calculation and work it as using similarity to case() function?
ASKER
Will, I looked over the List() function, I am not familiar with it. I looked at the examples, but can not grasp it. Maybe it is late in the night and I need some rest.
If(nad_usersettings_device
If(nad_usersettings_device
Using the List function might be complicated but it's hard to see what might work best without being able to see the files. If you you a calculation to reference a field in a related table, the result will always be whatever value is in the first related records. The List() function gives you a Return-delimited list of all related values, sorted by the Sort Order if one is specified for the relationship, or creation order if none is specified.
I still think the issues are that 1) your calculations, at least the phone and monitor ones, are using different relationships than the one you've been talking about to DeviceSettings, and 2) if there are multiple related records, your calculation is only going to show the value from the first related record. (If you want to see all related values you could insert the List() function into your calculation and make the field a bit larger so you can see the results (temporarily, just so you can better figure out what is going on).
I still think the issues are that 1) your calculations, at least the phone and monitor ones, are using different relationships than the one you've been talking about to DeviceSettings, and 2) if there are multiple related records, your calculation is only going to show the value from the first related record. (If you want to see all related values you could insert the List() function into your calculation and make the field a bit larger so you can see the results (temporarily, just so you can better figure out what is going on).
ASKER
Will, here is the corrected usersettings. I noticed I sent you an older picture when I was testing two methods.
I will attempt to play with the List() function today.
The relationship I mentioned from: NAD_UserSettings::NAD_User
uses contactID to pull related records with matching contactID and as well as any user working from "main" to any devices as deviceLocation of 'main".
I was using the calculation type on the field to retrieve records that ware either "phone" for the phone field, and monitor for monitor.
Can you provide me a sample List() function to be able to pull my values as per I am attempting to?
correctedUserSettings.JPG
I will attempt to play with the List() function today.
The relationship I mentioned from: NAD_UserSettings::NAD_User
uses contactID to pull related records with matching contactID and as well as any user working from "main" to any devices as deviceLocation of 'main".
I was using the calculation type on the field to retrieve records that ware either "phone" for the phone field, and monitor for monitor.
Can you provide me a sample List() function to be able to pull my values as per I am attempting to?
correctedUserSettings.JPG
ASKER
Will, I just checked and noticed that it pulls up the first device assigned to each specific user.
The List() function is simply List( RelatedTable::SomeField ) and results in a return-separated list of values for all related records. The value sort by the Sort Order as specified in the Relationship if one exists, otherwise they are in creation order. A calculation using
List( NAD_UserSettings_DeviceSet
Or any other field will give you a list of devices. If you want it to display as a comma-separated list, just use:
Substitute( List( NAD_UserSettings_DeviceSet
List( NAD_UserSettings_DeviceSet
Or any other field will give you a list of devices. If you want it to display as a comma-separated list, just use:
Substitute( List( NAD_UserSettings_DeviceSet
ASKER
I would like to close this question.
I forgot about this question, since this project for this question was concluded.
This was decided to be phased out.
Thank you for all that helped.
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.