Car owner's list, seems simple but I can't do it...

[David Maffucci]

Hello FileMaker Wizards,

I'm helping a car club with a task that either is beyond me or may be impossible given the structure of their poorly designed database. To ultra-simplify, I've exported all of three fields which are all we need to accomplish the task...here goes.

Every car has a 4 digit serial number, a current owner, and a list of past owners. All we want to do is generate a list, sorted by owner's name, showing all the cars they own or have owned in the past. If they currently own a car, it's serial would show as bold. That's all! It should look like this (fake data):

John Doe - 0075, 1736, 2567
Jane Smith - 0362, 1883, 3002, 3182

The problem is, the past owners is a big text field with no real structure. Some owners have middle names, some owners are actually companies, most have a location in parenthesis which should be removed, some have a year they owned the car. All that is irrelevant for the purpose of this list. There could be one past owner or as many as a dozen.

I know there are some text tricks that can be used to both isolate the lines in the text field and maybe even clean up the names but how to do it, and attach the serial, and identify them as past owners so the name is not bold is the hard part. 

Any thoughts on how to take this mess of a text field and break it up so we can just have the owner's name? To help make this easy for you to understand and offer your saga advice, I'm attaching the database here so you can see it...I appreciate any advice or tips you guys can offer.

Thank you,

David

Owners List.fmp12

[comment]

27 minutes ago, David Maffucci said:

generate a list, sorted by owner's name, showing all the cars they own or have owned in the past

For this, you should have three tables: Owners, Cars and a join table of Ownerships. So the real question is how to get there from your current position.

IIUC, your current table is the Cars table.

To create a table of Ownerships, you would need a script to loop over the Cars table, and do this:

• Place the ID of the car in a variable;
• Place the current owner's name in a variable;
• Place the list of past owner's in a variable;
• Go to a layout of the Ownerships table;
• Create a record for each line in the past owner's variable and populate its fields as follows: CarID = the carID variable; Name = the text before the opening parenthesis; Address = the text in parentheses; Period = the text after the closing parenthesis and semicolon;
• If the Period is empty, fill it with some arbitrary value;
• Create another record for the current owner and leave the Period field empty to signify current ownership.

To create the Owners table, you can import the Name field into a new table where it's validated as unique, validate always.

This should provide you with a half-decent starting point. Your next steps should be identifying similar names, and replacing names with meaningless IDs  for the match fields. You could also try and split the Period field into separate start and end fields. Ideally, a current ownership would be identified by not having a value in the end field.

---
P.S. Do note that you have a duplicate record for serial number 0075.

[David Maffucci]

Yikes, this is already beyond me. I've done plenty of relational databases but I'm not sure I understand the variable part.

Also, how do I break the owners list into separate owners? I was thinking it would be done using text functions like left and right and such...

I may need to go back to FileMaker School just to follow your very likely brilliant advice.

[comment]

22 minutes ago, David Maffucci said:

how do I break the owners list into separate owners?

The owner list is already broken into separate owners: each owner is on a separate line. You just need to grab each line in turn using the GetValue() function in a loop until the counter passes ValueCount ( $ownerList ).

 

22 minutes ago, David Maffucci said:

I'm not sure I understand the variable part

You use variables in a script to (among other purposes) pass values from one context to another. In the above example, you would have your script do:

Set Variable [ $ownerList ; Owner List::Past Owners ]

 

22 minutes ago, David Maffucci said:

Yikes, this is already beyond me.

It's a lot of work, but it's not that complicated. I have broken it into smaller chunks; you can ask additional questions about each step.

---
P.S. Please edit your user profile to reflect your version and platform so that we know what you can use.

 

Edited November 3, 2020 by comment

[Agian510]

Hey,

I'm still new to Filemaker and "comment" has already said this but it is important to have ID numbers or "primary keys" set up for not only your individual cars (which seems to be set up as the 4 digit numbers) but also for each owner. Looking at your file you currently have one table to do all these things, which usually doesn't end up with the results you want in Filemaker solutions. 

So you would need a "Car Owners" table and each individual car owner would have their own "Primary Key". So in your Car Owner table, you would create a record for each person and they would each be assigned their own Primary Key. This works great especially as I see in your file you have multiple people named Mark. Example: Mark Lieb primary key would be a generated serial number "001" and Mark Kerr would be "002" and so on for each owner. This may be tedious but not a difficult task.

You would have your "Car" table and this is where you would put specific details about each car, based on the 4 digit serial numbers you already assigned.

Then you can have a main table AKA the "Ownership" table. This is where you might want to have a portal, relating to your "Car Owners" field and in the portal you would list, based on the car serial number, it could generate a list of all the previous owners, based not on their names but their Primary Key numbers.

I hope this is a good starting point, please point out any errors in my line of reasoning. The good and bad thing about filemaker pro is that there is no one way to do the task you need it to do, but it is so open that it is difficult, particulary for beginners to figure out how to do the setting up of Solutions.

Good luck,

Alex