Forum Discussion

JeffPeterson1's avatar
Qrew Captain
3 years ago

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:

[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)))),


The other is [REGIONAL FSC] and holds the user for a given region and the code looks like this:

[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

No RepliesBe the first to reply