A way to use Formula Queries to Assign Regions

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
------------------------------