FileMaker has "stored calculated fields" which you could use for this. (There are other options as well, like using SQL to look up the user)
But going the stored calculated field route, you can make a "compound" key field. Simply do
PersonalKey = LIST ( Personal Reg::ALPS ID ; Personal Reg::GE SSO ) this wil put the alps id and the ge sso id on two seperate lines. In older version of FileMaker you need to do Personal Reg::ALPS ID & "¶" & Personal Reg::GE SSO.
Make sure the calculation result is text and that storage is set to stored. This field will then have both keys seperated by a ¶ and that is actually your OR.
In the RegIT table make a field called PersonalKey
Then set the relationship to
PersonalKey = PersonalKey
But how does that work when the asset manager for example change the owner of an asset. she then for example set the field "SSO" with a new SSO value. does it then know to change the other related fields ? or do i need to do something so it does ?
And how do i show the values of PersonalKey, do i create 2 fields and calculation on them with getValue(PersonalKey;1) for example ?
Sorry for all the noob questions =) just want to make sure i know what am going before i start =D
If you want to have a stored calculation you can either
Use a Script Trigger that changes value after a field have been changed
Have a FileMaker Server Scheduled Script run say every 30 minutes to re calculated fields
Have a stored calculation, but using that in a relationship is not going to give you a fast solution (relationship portal)
The "stored" vs "unstored" only means when does FileMaker calculate the value. If a calculation is stored it will change the values if one the fields in that calculation changes. If it is unstored it will calculate the field whenever the field is needed (which is slow but uses less space)
Be sure to set the entire field to a calcuation though, not the auto-enter option.
In the past we used to have huge fields like this for example
left(name;1) & ¶ &
left(name;2) & ¶ &
left(name;3) & ¶ &
Leftword(name;1) & ¶ &
Middlewords(name ; 2; 1)
It works very fast actually! (just uses more space) Cause stored fields don't need to be recalculated only when actually changing a name, or key in your case.)
If in Personal Reg SSO is changed the Calculated key will be updated. And when you change a value in RegIT it will relookup.
You can use the SSO and ALPS fields as usual in the PersonalReg table. You could use GetValue as you suggested as well.
But how does it really works then with the key.. PersonalKey
lets say an asset has the following:
PersonalKey (1111111; 45454545)
ALPSID = "1111111"
SSO = "45454545"
And the Asset Manager change the SSO to "2222222" doesnt that then look for the relationship with
the PersonalKey (1111111;2222222) and that dont exist, since its 2 different users.. ? or does it then ignore the "1111111" ? or do i manually need to script so it removes the "1111111" ?
I suggest that you carefully consider using neither value as your key in the relationship. Use an ID that uniquely identifies either the user or their computer that is neither externally supplied value but an internally generated ID value such as a serial number or the text returned by Get ( UUID ).
This then simplifies your relationship and also avoids issues that can arise if someone accidentally enters the wrong data as the ALPID or SSO and the error is not immediately detected--which can lead to problems where correcting the error disconnects related records.
i agree.. but since we are adding users from a table of 2500 users its kind of hard to know what ID they have. we do know what SSO or ALPSID they. And by adding another step with users search will only make the procedure slower i think :/
i might be wrong though.
Well one option (and there are many to this question :-)) is to have in the layout where the user enters a SOS or ALPSID, then with a script trigger, perform an executeSQL statement that select the "new" id field and use that in a relationship.
Other is in the layout just have one field with the label SSO/ALPID and with the calculated field in the source database, either will work. Point is we used that trick for years, before SQL came out and filtered portals etc. It's one of typical filemaker things (compound key fields) and if the calculation is stored it won't have noticeable impact on speed only a bit more storage space.
Well isn't an asset only assigned to one person? So it would never have and an SSO and an ALPSID? In the asset table it is either SSO OR ALPSID. You need the calculated key in the Personel Table.
You can use a calculated field in the asset table as well, just make sure that when SSO is filled ALPSID is erased and vice versa.
Yes, but you can easily use either a name or one of these two ID values to find the appropriate user record quite easily and does not require an additional data entry step to do so.
to your question about ALPSID and SSO, every user have both. but depending on what ticket system we are recieving the ticket in its based on one of them and the other isnt showing. thats the problem.
So in my user info on each asset i need both to be showing. so the problem am having is to build fields that makes it possible to change one of them ALPSID or SSO and it updates information on the rest of the fields about the user.
ALPSID = 10
SSO = 32
Name = Carl
this asset is to be used by another user with SSO 20. So when i change the SSO to 20 rest of the fields updates with the user info for example: alpsID = 4, Name = Stefan. same happends if i instead use ALPSID to change.
The way am testing now is to create User fields in the asset table. as global. and triggers on exit of the fields ALPSID and SSO. but am having some problems to get to work properly and am not sure its the best way to do it.
Can one asset be used by more than one user?