Record Completion Indicator

[innodat]

Here's a challenge!

I would like to add a visual indicator to my database which gives the user feedback on the level of completion of each record - in other words, how many fields contain information, and how many related records exist.

The result could be a simple HTML status bar which varies in length and color: red and short for content in few fields and few related records, green and long for content in most fields and lots of related records.

I'm assuming that a custom function would be the way to go?

How do you collect such information on fields and related records without slowing down the database? How could the result be translated into a percentage of completion and HTML status bar?

Any genius ideas?

[Peter (duksis3)]

[comment]

How could the result be translated into a percentage of completion

It depends: with local fields, it's very easy because you know how many of them are there. Let's say 10 fields in total, so if 4 of them are filled that's 4/10 = 40%.

It's a bit more complex with related records, because the target is moving. Say a parent record has 4 related records in the child table - you need to know the maximum of children for any record in the parent table (or in the found set??) in order to calculate the percentage.

[innodat]

Interesting! I wish there was a way without script triggers...

Is anyone familiar with Nightwings UltraLog? That seems to collect information in fields without the need for triggers. A auto enter field is added to each table:

[color:blue]UltraLog( LogData; ModStamp; "

First Name¶Last Name¶...

")

And a custom function seems to do the heavy lifting.

[color:blue]// SYNTAX: UltraLog ( LogField; ModTime; FieldsToLog )

// ORIGIN: http://www.nightwing.com.au/FileMaker

// NOTES: Transactional (IWP Compatible) Audit Log Function.

// VERSION: 1.0v3

// © 2009 Ray Cologon, NightWing Enterprises, Melbourne, Australia

Case(ValueCount(FieldsToLog) and not $ClearLogPermit;

Let([

Fnm = GetValue(FieldsToLog; 1);

Pref = Position(LogField; " " & Fnm & " "; 1; 1);

Pst = Position(LogField; " -» "; Pref; 1) + 4;

Pnd = Position(LogField & ¶; ¶; Pref; 1);

Pval = If(Pref; Middle(LogField; Pst; Pnd - Pst); "[---]");

Fval = GetField(Fnm);

Sval = Substitute(Fval; ¶; "‡");

Nval = If(Length(Fval); Sval; "[null]");

vInit = Pref = 0 and IsEmpty(Fval);

Prep = Exact($ClearLogPermit; "0")];

If(not Exact(Pval; Nval) and not vInit;

TextStyleAdd(TextColor(

GetAsDate(ModTime) & " " & GetAsTime(ModTime) & " " &

If(Prep; "[Log Prep]"; Get(AccountName)) & " " &

Fnm & " " & Pval & " -» " & Nval & ¶; Prep*11776947); Prep*Italic)) &

UltraLog(LogField; ModTime; RightValues(FieldsToLog; ValueCount(FieldsToLog) - 1))

);

LogField

)

I wonder if this (or ideas from it) could be used here to bypass triggers?

PS: I hope I didn't violate any rules by posting their function - if so I remove it and post a link to their sample file.

[innodat]

For related records: what about this?

Count (ChildTable::Record ID Field)

I guess this could be done for each relationship, and the result weighted somehow and added to the number (percentage) from local fields?

Note: Of course this would only account for first level of related records (immediately related) - but that might be sufficient for this.

[comment]

I wish there was a way without script triggers...

Who said anything script triggers? All you need is an unstored calculation counting the child records, and either a Max summary field (if you want to consider the found set only) or an x self-join relationship of the parent table.

[innodat]

This is what confused me:

I think that you need to add field containing filled field count (probably via script triggered from record commit (?) to increase number by 1 if field isn't empty anymore) and maximum must be filled number.

Can you point me into a direction for a good formula?

Taking a risk to make a fool out of myself...

Let (

A = If (IsEmpty(Field X); 0; 1);

B = If (IsEmpty (Field Y); 0; 1);

...;

A + B...

)

[comment]

You still haven't said if you want to consider the found set only or the entire parent table.

[innodat]

I believe I lost you...I'm not sure if I understand where the found set would come in?

I would like to look at each record in the parent table individually, and add information about the count of related records to each record of the parent table.

Edited January 6, 2010 by Guest

[comment]

As I tried to explain in my first post, it is not enough to look at an individual parent record. Say one parent has 10 children, another only 5. So the first parent is at 100%, and the other is at 50%. Some time later, the first parent has 100 children, the other parent has 25. So now the second parent is only at 25%.

That is unless you have some sort of cap on the maximum number of children.

[innodat]

Ah, I understand now. Thank you for explaining!

We would need to look at the entire parent table, not just the found set. However, a cap is entirely possible. We could set it at 10 child records, for example.

In fact, there I believe there should be a cap. Since unlimited child records can be added, it makes sense to specify a number which equals 100%. It's like the specified goal/quota is reached.

[comment]

If there's a cap then =

Count ( Child::ParentID ) / cap

will give you the percentage.

---

or =

Min ( Count ( Child::ParentID ) / cap ; 1 )

if you don't want to exceed 100%.

Edited January 6, 2010 by Guest

[innodat]

Great, thanks!

And I'm assuming for the fields on the parent record itself I'm just collecting binaries as suggested in the Let function above?

Thank you for your patience in explaining - I'm learning a lot.

[comment]

I'm assuming for the fields on the parent record itself I'm just collecting binaries as suggested in the Let function above?

No, it's simply =

Count ( Field 1 ; Field 2 ; Field 3 ; ... ; Field N ) / N

[innodat]

Amazing simplicity. Thanks again! & Happy New Year!!

[innodat]

Afterthought...

When I'm adding the html code to list view, it slows down scrolling significantly. This is probably because of the loading time of the web-viewer. It might be better to use an old fashioned bar with a repeating field.

[comment]

How about something like:

Left ( "gggggggggg" ; barSize )

formatted in Webdings font?