Separate Lines From Repeating Field

[Rick Miller]

I am trying to extract lines entered in a repeating text field (named "Equip" w/10 reps) into separate fields (Equip1...Equip 2...etc)

I am trying to modify (add to) a large existing database with alternate layouts so I can manage all the data better.

I would assume a calculation or script to populate the alternate layout fields with the extracted data...  Or perhaps a better idea?

Any help is greatly appreciated!

 

[comment]

8 minutes ago, Rick Miller said:

I am trying to extract lines entered in a repeating text field (named "Equip" w/10 reps) into separate fields (Equip1...Equip 2...etc)

I think that would only worsen your situation, not make it better. If an object can have 10 pieces of equipment (or indeed any number of items of kind), the correct solution is to use records in a related table.

Such migration is easy to perform by importing the existing records into a new table and selecting the "Split values into separate records" option for the repeating field/s. You need to also import the original record's unique ID, and link the two tables based on that.

The above is assuming that by "separate lines" you mean values entered in separate repetitions of the repeating field - not actual lines (paragraphs) entered in the same "cell".

[Rick Miller]

Thank you for the quick reply. Allow me to expand on my goal...and (Yes, the data in the repeating fields are values entered in separate reps.)

This database is very large, and live (125K+ records) It's for gear needed for meetings. Each record is an "invoice" of gear. 

I would like to be able to "mine" the data in two existing, repeating fields (10 reps;  "ID", which happens to be the primary key, and "Equip Description") so I can track amount of gear in use, and gear needed for future events.(Related table is just an inventory list, related by the ID code)

I am assuming I need to separate the repeating fields in order to get a running count, or is there a way to do this with the existing structure?

Note:This database was not set up to track the gear very well, but it's what I have to work with...All fields are in one table except the gear list.  (There are many other fields for random data, but not of concern to my project)

Due to the large number of existing records I don't thing migration is workable, although I like the idea. 

I hope I am on the right track here, and thank you for any help or guidance. 

Please let me know if I need to clarify...

[comment]

I don't see what the size of your database has to do with this. If you hope to get any meaningful statistics from your data, you need to abandon your current structure and move to a proper relational model. Records can be found, sorted, grouped and summarized at will. 

[LaRetta]

Hi Rick,

Using repeating fields is the number one mistake made by those new to database design.  Number two mistake is using several fields (each containing like items) as you described (Equip1, Equip2).  Using separate tables all within the same file is the simplest solution and I second Comment's suggestion ... switch now.  If you do not, you will later wish you did and it will become much more difficult.  Once you use tables, you will begin to understand the POWER of relational design.

Welcome to FMForums!  It will also help if you complete your profile, particularly your FileMaker version and OS. 🙂

[Rick Miller]

Thanks so much for the advice.

For now, I cannot abandon the structure for several reasons I cannot discuss. I wholly agree with the opinions and am very aware of what I should be doing going forward (ie: rebuilding into a relational database), but the hands are kinda tied. (I inherited this project...just trying to help out...)  I'm working with FmPro 14/Windows 7, and cannot upgrade either at this time.  I've built some workarounds for other functions, but this one issue has been perplexing.

I think I will create a separate, truly relational database and then figure out a way to extract the one bit of info I need, (ie: the use on any given day of a piece of gear) from that repeating field in our current file.  Then I should be able to manage the inventory properly.

I am grateful for the help and suggestions...

[comment]

3 hours ago, Rick Miller said:

For now, I cannot abandon the structure

But you started by asking how to switch from a repeating field to 10 individual fields. I would call that a change in structure - the only problem is that the change is for worse, not for better.

 

3 hours ago, Rick Miller said:

the one bit of info I need, (ie: the use on any given day of a piece of gear) 

This bit is actually possible with your current structure (well, sort of). If you define a relationship matching a global date field (in any table) with the date field in your "invoices" table, so that you have a chain of relationship say:

SomeTable::gViewDate = Invoices::InvoiceDate

Invoices::EquipmentID = Inventory::EquipmentID

you can place a portal to Inventory on a layout of SomeTable to show all items that have been booked on the given date. To achieve the same thing with 10 individual EquipmentID fields would be significantly more complicated.

However, this will still not tell you how many pieces of the same equipment have been booked on the given date. Something that would be trivial to produce using a report from the table you don't have.

[OlgerDiekstra]

12 hours ago, Rick Miller said:

Thanks so much for the advice.

For now, I cannot abandon the structure for several reasons I cannot discuss. I wholly agree with the opinions and am very aware of what I should be doing going forward (ie: rebuilding into a relational database), but the hands are kinda tied. (I inherited this project...just trying to help out...)  I'm working with FmPro 14/Windows 7, and cannot upgrade either at this time.  I've built some workarounds for other functions, but this one issue has been perplexing.

I think I will create a separate, truly relational database and then figure out a way to extract the one bit of info I need, (ie: the use on any given day of a piece of gear) from that repeating field in our current file.  Then I should be able to manage the inventory properly.

I am grateful for the help and suggestions...

It's called Technical Debt. https://en.wikipedia.org/wiki/Technical_debt

https://samuelmullen.com/articles/the-high-cost-of-technical-debt/

And like financial debt, it accrues interest. The longer you leave it in place, the more expensive it becomes to get rid of. You pay the interest in time (which is essentially money). 

I have inherited a DB as well that accumulated a lot of technical debt and interest (10 years worth). It's a slow and sometimes painful process to turn it around. The best approach is to draw a line in the sand , and start turning things around, little by little. You don't have to create a new database per se, you can work within what there is now. Create new tables and relationships fit for purpose.

125k records isn't large. My database has almost 1 million records just for payments. 350k+ customers, double that in invoices. Almost 600 layouts. Probably more than half is redundant. 193 tables. It's a giant web but one that I understand (mostly).

 

Edited July 9, 2019 by OlgerDiekstra

[OlgerDiekstra]

This https://www.gizmodo.com.au/2019/07/7-elevens-bad-app-design-let-criminals-steal-more-than-500000/ could well be an excellent example of technical debt and the cost. Possibly the developers meant to go back to the reset process but pressure to deliver might have prevented this, or it may not have been documented and they simply forgot. Very expensive exercise.