Calculations/Scripts with Repeating Fields

[Joe_Schmo]

I'm updating an existing database to add some functionality and unfortunately, I'm stuck dealing with a lot of repeating fields. I know they can be a pain to work with and should be avoided but the database is already in use and there are years worth of records that I have to maintain backwards compatibility with. So, on to the question...

The database is for a log (similar to a captain's log) that has fields for date, time, operator's name, log comment, comment ID, and comment category. The date is a single field (1 repetition) but all the other fields are in repetitions of 30. So a given record is viewed by the date, with up to 30 log entries for the day.

I have added a second table with nearly identical fields that will be used to track outstanding issues (log entries that should be carried forward to the next days log, such as broken equipment). I would like the 2nd table for outstanding issues to auto-fill the first available blank repetition with any log entries from the daily log that are outstanding (Log:Category=Outstanding).

So the daily log should be used as it has been, and the outstanding issues table/layout will be used to view and store any daily log entries of importance (auto-entered whenever a daily log entry is created with Log:Category=Outstanding) that should always be viewable no matter what Date's daily log is being viewed. That way the operator doesn't have to go searching through all previous logs to find important issues that should be noted.

I think I have the problem about half figured out but the repeating fields are complicating things. I want to give every record in the daily log a unique comment # that is auto-entered as (Get (Log:Date) & Get (activerepetitionnumber)). The comment # field will be auto-filled via a script trigger when the comment text field is saved. Then, when the category is selected, a script trigger would copy the comment # and paste it in the first blank repetition of the outstanding issues:comment# and the rest of the fields would be lookups. The part I'm not sure about is how to reference the first blank repetition in the outstanding issues table.

[comment]

I'm stuck dealing with a lot of repeating fields.

I am not sure why you think so: breaking out repeating fields into separate records is quite easy - you just need to import the records into another file/table and select this option during the import dialog.

In any case, I don't see why you'd need another table to deal with the existing repeating fields. It seems it would be easier to add another repeating field in the same table to mark the outstanding issues.

[Joe_Schmo]

I am not sure why you think so: breaking out repeating fields into separate records is quite easy - you just need to import the records into another file/table and select this option during the import dialog.

This is just a reoccurring opinion I've found in several threads while researching the issue. Many people have stated that repeating fields are harder to work with when referencing them in calculations and scripts, and that they should be avoided. I'm not speaking from personal experience, because I have very little.

In any case, I don't see why you'd need another table to deal with the existing repeating fields. It seems it would be easier to add another repeating field in the same table to mark the outstanding issues.

I did add a field in the 1st table to allow a record to be marked as outstanding. The problem is, the log only shows that day's log entries so outstanding issues from previous days are not displayed. There could be outstanding issues spread across multiple records for the past weeks or months worth of logs. The solution my employer has tasked me with is to add tabs to the log layout; one tab for the daily log and one tab for outstanding issues. The daily log tab only shows that days log entries and the outstanding issues tab shows all log entries from all dates if the entry was outstanding.

It doesn't have to be a 2nd table, that's just the approach I have started with. As long as the outstanding issues tab always shows all outstanding issues regardless of the daily logs being viewed for a given date, then how it is accomplished don't matter.

I've attached what I have so far. There are some other tables that are no longer in use and a few other features I haven't mentioned that I am working on that can be ignored for now. I've given the guest account full access.

Hybrid Log2 Clone.fp7.zip

[comment]

Many people have stated that repeating fields are harder to work with

What I have tried to state is that converting them to proper structure (i.e. multiple records in a related table) is not too difficult - so you don't need to be "stuck" with them.

As long as the outstanding issues tab always shows all outstanding issues regardless of the daily logs being viewed for a given date, then how it is accomplished don't matter.

Perhaps it could be done like this (attached). Of course, eliminating the repeats is still the better option.

UnresolvedR.zip

[Joe_Schmo]

I'd much rather have individual fields anyway. How would I go about converting them?

[comment]

you just need to import the records into another file/table and select this option during the import dialog.

[Joe_Schmo]

sounds easy enough, but I won't have time to give it a shot until this evening. Thanks for the tip.

One thing that seems like it might be an issue later though-

Currently, the date is not a repeating field, and everything else is. So when I browse through records, I can view one date's worth of log entries on a single page of the log layout. Once imported into separate fields, I will just have a bunch of consecutive records that all have a date, log entry, etc... How can I get back to grouping and viewing them by date again? A portal that GRR from date perhaps? How would you set it up?

[comment]

How can I get back to grouping and viewing them by date again?

Place a portal to the new table on a layout of the original table, based on matching the date. Or, if the original table contains (now) nothing but the date, you could replace it with a global date field.

Edited November 21, 2011 by comment

[Joe_Schmo]

Ok, I created a new version of the DB and imported all the records to split the repeating fields into new records. I have a portal on the 2nd tab for the outstanding issues but I can't get it to filter the records shown in the portal to only show records with Log:Category=Outstanding. I only have one table now, as you suggested, but I have made a different TO for each tab of the layout and related them. so how do I filter the portal?

Nevermind, I figured it out. I made another table with a global field and set the value to "outstanding" then related the first two TOs through it.

Edited November 22, 2011 by Joe_Schmo