Most Efficient/Best Way?

[JTSmith]

I guess this is the best category...

 

I am redesigning my solution as I built it as a complete amateur and piled on my mistakes.  I have a table, Jobs.  It tracks move ins, move outs, vendors etc.  I have a "Warning table", filled with calculations that are related to my jobs table.  If a calculation is Yes, then on that particular Job record, I have a little flag container field show.  I have simple find buttons that find all records with a count of 1 or more, and a portal showing the warnings.  The table has calculations and summary fields so I can find flagged (warning) records.  If I decide to add a warning calculation, it's a bit of a headache.  I have to add the calc, add it to the summary fields, etc.  Ideally I would have a table of Warnings and I would add each calculation as a record.  Any insight on the best way to go about this?

 

Example warnings:

1. Move out is within 45 days from today

2. Move in is within 3 days and the cleaning vendor has not been scheduled

 

Any insight would be appreciated, I'm sure there is a much better way to go about it.

[comment]

Can you explain your current arrangement in more detail? What does a record in the Warning table look like, and how are the two tables related?

 

I know it may seem irrelevant, since you are asking for a better arrangement - but I believe it would help.

[JTSmith]

Sure.  I have these tables:

Job -Fields are move in, move out, name, city, state, size, etc. 

Lease-Related to jobs, some jobs have property leases (I rent the property from the complex, re-rent to the client)  Some properties I own, so no lease.

Vendors -Related to jobs, some jobs have vendors I use.  Fields are vendor, name, number, start date, stop date, etc.

 

The current setup I have has timestamp fields for every activity.  So when I receive the customer's paperwork, I hit a button which posts the timestamp in the Receive Paperwork field.  

 

That one one the warning table, say "Warning 4" field, says "If timestamp is empty and move in date is within 3 days, then 1".  Then I have a summary field that adds up the Warning Fields.  Warning 1 + Warning 2 + Warning 3, etc.  If a job has 4 warnings, the summary field is 4 and I can search my jobs by warnings >0.  THen out of 200 jobs, I'll have X number open up and I can go through each one and see what the issue is and proceed accordingly.  

 

If I decide I need a new warning, I have to add that field in the warning table, then add it to the summary, then add a field for Manual Override.  THe override is simply a field, Yes or No, for each warning calc.  It defaults to No, but if it's a warning I don't care about, I can check the box in the Job and it doesn't count that.  So my actual warning field says "If timestamp is empty and move out is within 3 days, then 1, unless the Warning Override field is set to Yes.  

[Fitch]

It sounds like your Warnings should be records, not fields.

[JTSmith]

It sounds like your Warnings should be records, not fields.

 

I would love that, I just don't know how to make each record a calculation...

[Fitch]

Put your warning logic into a script. When you click the button to timestamp the paperwork, run the script that checks the various conditions, and if one is met, creates a related record in the warning table. So you wouldn't have warning 1, warning 2, fields etc. -- just as many warning records as needed, each with perhaps a warning type and a date or timestamp, and your override flag.

I would make a "reverse" override, i.e., a field that auto-enters 1 by default. Make it a checkbox with a value list of 1, and uncheck the ones you don't want to count. Your "summary" would be a calculated Sum of that field.

[comment]

You still didn't say what is the relationship between the Jobs and Warnings tables, and I am struggling to see why you need the Warnings table at all; it seems like the relationship is one-to-one.

 

 

Here's a way to streamline the operation that (I think) you already have, before suggesting something radically different:

 

Define a single cWarnings calculation field in the Jobs table. For starters, let's make the result type a Number and the formula will look something like this:

IsEmpty ( Field A ) and Field B > 15
or
Field C < Get (CurrentDate ) and not Field D
or
...

This will return 1 if any of the conditions is true. Now let's add an override mechanism. For this, let's have a text field named OverrideWarnings, formatted as a checkbox, using a value list listing the text values of all possible warnings. So:

IsEmpty ( Field A ) and Field B > 15 and IsEmpty ( FilterValues ( "first warning" ; OverrideWarnings ) )
or
Field C < Get (CurrentDate ) and not Field D and IsEmpty ( FilterValues ("second warning" ; OverrideWarnings ) )
or
...

Now, why not make use of the fact that each warning has a text form, and output a list of the warnings in effect, instead of just a 1 or a (rather meaningless) count of the warnings:

List ( 

Let ( [
warning = "first warning" ;
test = IsEmpty ( Field A ) and Field B > 15
] ;
Case ( test and IsEmpty ( FilterValues ( warning ; OverrideWarnings ) ) ; warning )
) ; 

Let ( [
warning = "second warning";
test = Field C < Get (CurrentDate ) and not Field D
] ;
Case ( test and IsEmpty ( FilterValues (warning ; OverrideWarnings ) ) ; warning )
) ;

...
)

The result here needs to be Text, of course.

 

So now you'll have a nice list of all applicable warnings and you can also search the Jobs table for jobs with specific warnings only, as well as any warnings at all (by entering * as the search criteria).

[JTSmith]

I think I'm missing something, but I'm close...

 

I defined cWarnings, and I used this formula to start:

 

occupants > 3 and size = "One Bedroom, One Bath"  (unstored)  [ 8 people in a one bedroom is a problem, so it tells me]

 

That works.

 

I'm not sure on the value list part...  When I create OverrideWarnings, value list options are Custom or Values from Field.  Not sure if I need to do a value, or manually list (first warning, second warning, etc?)  If it's a value, I can't really do cWarnings because it's an unstored calc right?

 

Thanks!

[comment]

On its own, the cWarnings calculation could be stored - but then you are bound to use Get (CurrentDate) in it, so no, it can't. IIRC, there's a workaround that would allow you to use it as the source for a value list anyway, but there's another issue that needs to be considered:

 

Suppose you have a warning named "Overcrowded". And suppose there are two jobs for which this warning applies. And suppose you override the warning on both these jobs. As a result, the value is no longer returned by the cWarnings field in any record, and therefore is no longer included in the value list. In those two records, the OverrideWarnings field still contains the value, but it is no longer being displayed. It will return as soon as any record gains an "Overcrowded" status, so this is mostly a cosmetic issue. Still, with this solution, it is either that or manage a custom value list in parallel with the cWarnings field.

[comment]

 it is either that or manage a custom value list in parallel with the cWarnings field.

 

Come to think of it, there is a third option:

 

1. Let cWarnings calculate the warnings regardless of OverrideWarnings;

2. Use a second calculation field to suppress the warnings that are checked in OverrideWarnings (requires a custom function).

[JTSmith]

I'll test this out tonight, thanks!