I thought I would share a method I found to use Formula Queries to create an easily editable list of regional assignments for my users, without have to create any table-to-table relationships.
I have two tables involved in this: Sites and FSC Assignments.
Sites are a list of work sites and I needed a way to be able to give them regional assignments, but also to be able to edit these assignment easily.
What I did was to create the table called 'FSC Assignments' and it holds only 1 record which is a list of all of the states and territories we work in and a list of my users regional assignments. It looks like this (I made a simple form to hold the fields neatly):
Then, back in my Sites table, I have 2 fields using Formula Queries to grab the Region from the STATE/TERRITORY section and the user from the FSC REGION ASSIGNMENT in the single record of the FSC Assignments table.
[Assigned Region] is just a Formula Text field and the code looks like this:
If(
[State]="Alabama",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),65)))),
[State]="Alaska",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),66)))),
[State]="Alberta",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),67)))),
[State]="Arizona",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),68)))),
[State]="Arkansas",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),69)))),
[State]="British Columbia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),70)))),
[State]="California",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),71)))),
[State]="Colorado",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),72)))),
[State]="Connecticut",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),73)))),
[State]="Delaware",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),14)))),
[State]="District of Columbia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),15)))),
[State]="Florida",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),16)))),
[State]="Georgia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),17)))),
[State]="Hawaii",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),18)))),
[State]="Idaho",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),19)))),
etc...
The other is [REGIONAL FSC] and holds the user for a given region and the code looks like this:
If(
[Assigned Region]="Region 1", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),7)))),
[Assigned Region]="Region 2", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),8)))),
[Assigned Region]="Region 3", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),9)))),
[Assigned Region]="Region 4", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),10)))),
[Assigned Region]="Region 5", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),11)))),
[Assigned Region]="Region 6", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),12)))),
[Assigned Region]="Region 7", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),13))))
)
The result is this:
The region and regional FSC are picked from that single record in the FSC Assignments table.
Should we need to change any of those, a user can simply edit the record and the changes will apply without any need to do anything else:
I did not need to make any table-to-table relationships, though getting the code right took a little bit of tweaking.
I don't know that this is the best way to do this, but it definitely works. I'm curious if anyone has any feedback on this method. Maybe it can be improved.
------------------------------
Jeff Peterson
------------------------------