innodat Posted January 5, 2010 Share Posted January 5, 2010 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? Link to comment Share on other sites More sharing options...
Peter (duksis3) Posted January 5, 2010 Share Posted January 5, 2010 Link to comment Share on other sites More sharing options...
comment Posted January 5, 2010 Share Posted January 5, 2010 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. Link to comment Share on other sites More sharing options...
innodat Posted January 5, 2010 Author Share Posted January 5, 2010 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. Link to comment Share on other sites More sharing options...
innodat Posted January 5, 2010 Author Share Posted January 5, 2010 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. Link to comment Share on other sites More sharing options...
comment Posted January 5, 2010 Share Posted January 5, 2010 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. Link to comment Share on other sites More sharing options...
innodat Posted January 5, 2010 Author Share Posted January 5, 2010 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... ) Link to comment Share on other sites More sharing options...
comment Posted January 5, 2010 Share Posted January 5, 2010 You still haven't said if you want to consider the found set only or the entire parent table. Link to comment Share on other sites More sharing options...
innodat Posted January 6, 2010 Author Share Posted January 6, 2010 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted January 6, 2010 Share Posted January 6, 2010 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. Link to comment Share on other sites More sharing options...
innodat Posted January 6, 2010 Author Share Posted January 6, 2010 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. Link to comment Share on other sites More sharing options...
comment Posted January 6, 2010 Share Posted January 6, 2010 (edited) 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 Link to comment Share on other sites More sharing options...
innodat Posted January 6, 2010 Author Share Posted January 6, 2010 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. Link to comment Share on other sites More sharing options...
comment Posted January 6, 2010 Share Posted January 6, 2010 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 Link to comment Share on other sites More sharing options...
innodat Posted January 6, 2010 Author Share Posted January 6, 2010 Amazing simplicity. Thanks again! & Happy New Year!! Link to comment Share on other sites More sharing options...
innodat Posted January 6, 2010 Author Share Posted January 6, 2010 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. Link to comment Share on other sites More sharing options...
comment Posted January 6, 2010 Share Posted January 6, 2010 How about something like: Left ( "gggggggggg" ; barSize ) formatted in Webdings font? 1 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5232 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now