Repeating Field to Portal Challenge

[Darren Emery]

I have a rather old inspection db that was built with repeating fields for result entry. This db has been in use since the very early 90s, perhaps a bit before, so I don't hold much against the db admin that set this up.  The did the best with what they had!

 

I've known for some time that I need to convert these repeating fields into related records, and now is the time.  But - I've hit a snag.

 

I have found plenty of guidance on how to do the basic export/import as related records.  Pretty simple. 

 

My challenge is:  for each of the 27 repeating fields, there has been an inspection type assigned to that partiular field.  For example, if we enter a comment about the inspection on repetition # 10, that is always a comment about an electrical inspection.  The only way this is indicated to the user is by location on the layout.  There are no identifying markers in the field.

 

My goal is to populate a new field on the related records indicating what type of inspection it was.  How can I assign a value to a new field, in the new record, based upon the repitition count of the repeating field?  (I"m guessing I must do this at export, rather than import).

 

Ideas? 

[comment]

I got lost trying to follow your description:

 

or each of the 27 repeating fields, there has been an inspection type assigned to that partiular field.  For example, if we enter a comment about the inspection on repetition # 10, that is always a comment about an electrical inspection.

 

If you enter a comment in repetition #10, does it not relate to the object (inspection?) described in repetition #10 of the 27 repeating fields?

[Darren Emery]

I got lost trying to follow your description:

 

 

If you enter a comment in repetition #10, does it not relate to the object (inspection?) described in repetition #10 of the 27 repeating fields?

 

I guess the problem is that there is no object "inspection" - these are all on a single record "permit"  I need to break each repeating field into a unique related record, classified by inspection type.

[Darren Emery]

I got lost trying to follow your description:

 

 

If you enter a comment in repetition #10, does it not relate to the object (inspection?) described in repetition #10 of the 27 repeating fields?

 

I wondered if my description was hard to follow.  I had a long, detailed explanation all typed out, but lost it due to an errant keystroke.  I will reply with a (hopefully) more concise and accurate statment and question in the near future.  Almost 5pm here in KS, and it's been a long week.

 

Thanks comment - and all that view/respond.

 

Darren

[comment]

I guess the problem is that there is no object "inspection" - these are all on a single record "permit"

 

Well, there is some object in real life that a single repetition is supposed to represent, isn't there? You haven't told us what that is, and all we know (now) is that the object is a child of Permits and that it has 27 repeating fields describing it.

 

That's all pretty standard, but you describe some additional complication, and I don't understand what that complication is. You seem to be saying that data in repetition #10 of one specific repeating field is somehow significant to all the objects that are children of the current permit. Which doesn't seem very likely.

 

Perhaps you meant that the repetition number corresponds to some attribute of the object, that isn't described anywhere else? For example, all objects in repetitions 1 - 10 are of Type A, repetitions 11 - 20 are of Type B, and repetitions 21 and higher are Type C? That would make sense of sorts - and can be easily solved, too.

[Darren Emery]

Perhaps you meant that the repetition number corresponds to some attribute of the object, that isn't described anywhere else? For example, all objects in repetitions 1 - 10 are of Type A, repetitions 11 - 20 are of Type B, and repetitions 21 and higher are Type C? That would make sense of sorts - and can be easily solved, too.

 

I believe the above hits the nail on the head.  However - each repetition is a new type.  1 is type footing, 2 is type electrical, 3 is plumbing, etc.

 

My goal is to end up with each of these repetitions as a new child record of the parent permit, specified by type, based upon the repetition number. 

[Darren Emery]

 

That would make sense of sorts - and can be easily solved, too.

 

This is very encouraging to hear.  I certainly look forward to hearing a bit more.  Thanks for sticking with me, and seeing the necessary trees through the forest, given my poor description!

[comment]

You just need to add a (repeating) calculation field to calculate the type. And you need to make it return a result only if the repetition is being used - otherwise you will be importing a lot of records with type only. So pick any one of the repeating fields that cannot be empty unless the repetition itself is entirely empty, across all the repeating fields, and make your calculation =

Case (
not IsEmpty ( Somefield ) ;
GetValue ( "footing¶electrical¶plumbing¶add¶more¶types¶here" ; Get ( CalculationRepetitionNumber ) )
)

Now you can just import the records into another table, with the option to split repetitions into separate records - and each imported record will have a type field with the value corresponding to the original repetition number.