Value of one field depends on other fields (1 Viewer)

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
Hello Everybody,

I have a field in my data base called status and want it to be updated automatically based on the input in certain fields.
For example:
If order sent field is checked, status field should show Phase 1
If order sent field + order shipped field is checked, status should show Phase 2
If order sent field + order shipped + Installation done field is checked status should show Phase 3
and so on.

Thanks.
 

GPGeorge

Grover Park George
Local time
Yesterday, 17:28
Joined
Nov 25, 2004
Messages
1,776
This could legitimately be a DISPLAY ONLY control on a form, but this should not be saved as an additional field in the table. The risk of going out of synch with the other fields is too great, and it does violate one of the rules of good database design, i.e. the Rules of Normalization.
 

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
This could legitimately be a DISPLAY ONLY control on a form, but this should not be saved as an additional field in the table. The risk of going out of synch with the other fields is too great, and it does violate one of the rules of good database design, i.e. the Rules of Normalization.
Thanks for your comments.
What do you suggest instead keeping in mind that at the end of the day I would like to know which order is in which phase ?

Thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:28
Joined
Sep 21, 2011
Messages
14,048
I would write a function to calculate the status and refer to that in the control?
 

GPGeorge

Grover Park George
Local time
Yesterday, 17:28
Joined
Nov 25, 2004
Messages
1,776
Create an unbound control on the form. Make its control source:

=Switch([InstallationDone]=True And [OrderShipped]=True And [OrderSent]=True,"Phase 3",[OrderShipped]=True And [OrderSent]=True,"Phase 2",[OrderSent]=True,"Phase 1")

Doing it this way forces all three checkboxes to be evaluated for every record to avoid errors that could otherwise occur.

Now, toggle the various checkboxes on and off in random order. You'll see why trying to save this as a stored value could easily result in out-of-synch values. There is nothing to stop a user from clicking "Installation Done" without checking either of the other two, or unchecking them after checking the "Installation Done" checkbox. Of course, we'd not expect users to be so careless, and it's part of every interface design to account for such things. I mention in the context of why we don't save values that depend on other values in the same record.

Actually, if this were MY project, I'd have two fields in a "ProjectHistory" table, one for the date and one for the activity. e.g. "July 27, 2022", "OrderSent". From that you always have a positive record of the activities along with the actual date they were completed. "Phase" can be calculated for display from that.
 

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
Create an unbound control on the form. Make its control source:

=Switch([InstallationDone]=True And [OrderShipped]=True And [OrderSent]=True,"Phase 3",[OrderShipped]=True And [OrderSent]=True,"Phase 2",[OrderSent]=True,"Phase 1")

Doing it this way forces all three checkboxes to be evaluated for every record to avoid errors that could otherwise occur.

Now, toggle the various checkboxes on and off in random order. You'll see why trying to save this as a stored value could easily result in out-of-synch values. There is nothing to stop a user from clicking "Installation Done" without checking either of the other two, or unchecking them after checking the "Installation Done" checkbox. Of course, we'd not expect users to be so careless, and it's part of every interface design to account for such things. I mention in the context of why we don't save values that depend on other values in the same record.

Actually, if this were MY project, I'd have two fields in a "ProjectHistory" table, one for the date and one for the activity. e.g. "July 27, 2022", "OrderSent". From that you always have a positive record of the activities along with the actual date they were completed. "Phase" can be calculated for display from that.
Sounds Great. Will try.
Appreciate your valuable input and thank you very much.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:28
Joined
Feb 28, 2001
Messages
27,001
What do you suggest instead keeping in mind that at the end of the day I would like to know which order is in which phase ?

First, remember that this status is dynamic and might change a lot, but second it is also deterministic in that by looking at a few other fields, you can always correctly determine its value. This makes it a prime candidate for either a function OR use the more complex SWITCH function offered above by GPGeorge. BUT ... don't store it in a table. Display this status from a query. Queries are your friends and can be used as data sources for forms and reports. You could easily display this query in a datasheet view if you wanted a list of orders and phases. The only down side is that you cannot update a query that outputs a function.

For that reason, I might prefer to devise a VBA function to compute this status and do NOT include it in the table in any way. Instead, use it to define the values of an unbound control. The issue isn't that you can't build the function or use it. But you can't STORE it because a function doesn't store data. Not one of its abilities.

Code:
Public Function ShowPhase( [OrdSent] as Boolean, [OrdShipped] as Boolean, [InstDone] as Boolean, ... ) As String
Dim SP as String

SP = "Not Sent"
If [OrdSent] Then                  'If order sent field is checked, status field should show Phase 1
    SP = "Phase 1"
    If [OrdShipped] Then       'If order sent field + order shipped field is checked, status should show Phase 2
        SP = "Phase 2"
        If [InstDone] Then         'If order sent field + order shipped + Installation done field is checked status should show Phase 3
            SP = "Phase 3"
                If [and-so-on] Then ...                   'and so on.
            End If
        End If
    End If
ShowPhase = SP
Exit Sub

Put that in a general module so that you can call it from anywhere in your project.
 

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
First, remember that this status is dynamic and might change a lot, but second it is also deterministic in that by looking at a few other fields, you can always correctly determine its value. This makes it a prime candidate for either a function OR use the more complex SWITCH function offered above by GPGeorge. BUT ... don't store it in a table. Display this status from a query. Queries are your friends and can be used as data sources for forms and reports. You could easily display this query in a datasheet view if you wanted a list of orders and phases. The only down side is that you cannot update a query that outputs a function.

For that reason, I might prefer to devise a VBA function to compute this status and do NOT include it in the table in any way. Instead, use it to define the values of an unbound control. The issue isn't that you can't build the function or use it. But you can't STORE it because a function doesn't store data. Not one of its abilities.

Code:
Public Function ShowPhase( [OrdSent] as Boolean, [OrdShipped] as Boolean, [InstDone] as Boolean, ... ) As String
Dim SP as String

SP = "Not Sent"
If [OrdSent] Then                  'If order sent field is checked, status field should show Phase 1
    SP = "Phase 1"
    If [OrdShipped] Then       'If order sent field + order shipped field is checked, status should show Phase 2
        SP = "Phase 2"
        If [InstDone] Then         'If order sent field + order shipped + Installation done field is checked status should show Phase 3
            SP = "Phase 3"
                If [and-so-on] Then ...                   'and so on.
            End If
        End If
    End If
ShowPhase = SP
Exit Sub

Put that in a general module so that you can call it from anywhere in your project.
Thank you sir for your valuable input, much appreciated.
 

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
Sounds Great. Will try.
Appreciate your valuable input and thank you very much.
Create an unbound control on the form. Make its control source:

=Switch([InstallationDone]=True And [OrderShipped]=True And [OrderSent]=True,"Phase 3",[OrderShipped]=True And [OrderSent]=True,"Phase 2",[OrderSent]=True,"Phase 1")

Doing it this way forces all three checkboxes to be evaluated for every record to avoid errors that could otherwise occur.

Now, toggle the various checkboxes on and off in random order. You'll see why trying to save this as a stored value could easily result in out-of-synch values. There is nothing to stop a user from clicking "Installation Done" without checking either of the other two, or unchecking them after checking the "Installation Done" checkbox. Of course, we'd not expect users to be so careless, and it's part of every interface design to account for such things. I mention in the context of why we don't save values that depend on other values in the same record.

Actually, if this were MY project, I'd have two fields in a "ProjectHistory" table, one for the date and one for the activity. e.g. "July 27, 2022", "OrderSent". From that you always have a positive record of the activities along with the actual date they were completed. "Phase" can be calculated for display from that.
 

Fahad Ali

New member
Local time
Today, 03:28
Joined
Jul 27, 2022
Messages
7
Thank you Mr. GPGeoge your suggestion of Switch function worked perfect for my database.
Also thanks to Mr. The Doc Man.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:28
Joined
Feb 19, 2013
Messages
16,553
Late to the party but I agree with gpg - use dates, gives more Information - your formula will still work unchanged

On data input you should disable the phase2 and phase3 controls so they can’t be input until phase1 has been input. And phase3 should remain disabled until phase2 is completed
 

Users who are viewing this thread

Top Bottom