Data structure problem

[SwissMac]

I'm having difficulty working out the best data structure for my project. The logical structure I have is:

Client -< Factfind -< Asset -< "Snapshot" of values and other characteristics at regular points in time.

So, Clients are people. I go to see one and I take notes, this is called a Factfind.

In the factfind there are sections for Assets and Liabilities. Assets include Bank Accounts, Stocks and Shares, Mutual Funds, Houses. Although similar to each other, they have very different categories in which to record data, so a Mutual Fund has a Region it focusses on, a Bank Account does not. I need to record and keep for ten years all relevant information obtained at each visit (time when a new Factfind is created). It would be very useful to be able to update the fields in the different asset categories without erasing the old data. And I need to be able to print a summary so an auditor can check I know my clients financial situation - in some detail.

My FM knowledge is limited, I have just a single FM file to maintain and it only gets looked at about once every 4 years or so and I am struggling trying to work out how to organise things. 

Can you please help?

[comment]

Can you elaborate more on the fields that describe the various assets and liabilities? I get the impression that these fall into two categories - let's call them "permanent" and "varying".

For example, a bank account would have the bank name, account number and account type as permanent properties, while the current balance would be a property that varies from one inspection to another. So the question is how different are the varying properties from one asset/liability to another (perhaps all that's ever changing is their monetary value) and how different are the permanent properties (perhaps there are only a few fields that could be housed together in a single "wide" table)?

 

[SwissMac]

7 hours ago, comment said:

Can you elaborate more on the fields that describe the various assets and liabilities? I get the impression that these fall into two categories - let's call them "permanent" and "varying".

For example, a bank account would have the bank name, account number and account type as permanent properties, while the current balance would be a property that varies from one inspection to another. So the question is how different are the varying properties from one asset/liability to another (perhaps all that's ever changing is their monetary value) and how different are the permanent properties (perhaps there are only a few fields that could be housed together in a single "wide" table)?

 

Maybe these screenshots help? Only white fields are "permanent" as you put it. Yellow vary every time a Factfind is recorded. Blue indicate it is a Calculated field based on some of the data from the yellow fields and also vary over time. Green indicates data from a Drop down referencing values from a Value List. 

As you had told me earlier to put all the fields in one table I scrapped my earlier version with one table per asset class and put them all in one table called Assets. I don't understand your reference to a "wide" table.

[comment]

3 hours ago, SwissMac said:

Maybe these screenshots help?

Yes, they do.

Well, this is somewhat of a dilemma. You see, what you have is basically a survey. And typically a survey has a table of Questions where each question is a record, and every time someone takes the survey a record is created in an Answers table for every question they answer. 

However, such model would pose difficulties for you. It would be difficult to format some fields as edit boxes and others as checkbox sets or drop-down menus. And it would be even more difficult to perform calculations based on answers in separate records.

So taking this, along with your self-described skill level into account, I would suggest you make do with the following 4 tables:

The Assets table would hold the "permanent" fields; perhaps a better definition would be fields for which you do not need to keep history.

Now, every time an inspection is conducted, you would start by updating the Assets table - creating new records for assets added since last inspection and deactivating assets that ceased to exist. Once that is done, you would run a script to (a) create a new record in the Inspection table, related to the current client, and (b) create a record in the AssetInspections table for each active asset, related to the asset and to the current inspection.*

(*) The order of actions here is not important - as long as you end up with the same records, properly related to each other.

The AssetInspections table would hold the "varying" fields. It would be "wide" in the sense that all the "varying" fields, for all asset types, would be there. Naturally, you would want to have a separate layout for each asset type (you could use a OnRecordLoad script trigger to automatically select the appropriate layout). 

Finally, a note about printing (or viewing) a list of data collected during an inspection: clearly, there's no problem putting all the fields that are common to all asset types in a single layout; likewise, there should be no problem summarizing them using summary fields. The question is how to show type-specific fields on a single layout. This question has three possible answers: (1) overlay several fields and hide them conditionally so that only the fields relevant to record's asset type are displayed; (2) use calculation fields to display the relevant fields' data in each column; (3) study a technique known as "Virtual List".

Note also that for printing (or producing a PDF) you can print all assets of a type using one layout, then continue with another type using a different layout. The only drawback here is having a page break between types.

 

Edited November 15, 2021 by comment

[SwissMac]

6 hours ago, comment said:

I would suggest you make do with the following 4 tables:

The Assets table would hold the "permanent" fields; perhaps a better definition would be fields for which you do not need to keep history.

This is very helpful, I think you may have found the data structure I have been struggling to find. The devil is of course in the details.

When you say "for which you do no need to keep history" I assume you mean the fields for which varying values do not need to be kept, rather than fields which can be deleted once they are deactivated as being current assets? I need to keep all information, even deactivated assets, for a minimum of ten years.

 

Quote

Now, every time an inspection is conducted, you would start by updating the Assets table - creating new records for assets added since last inspection and deactivating assets that ceased to exist. Once that is done, you would run a script to (a) create a new record in the Inspection table, related to the current client, and (b) create a record in the AssetInspections table for each active asset, related to the asset and to the current inspection.

Would this all be happening on a single layout with fields displayed from the relevant tables? I can't visualise how it would look/work? At the moment I have the above layouts and in the Property layout the two Radio Button fields will cause relevant fields to be hidden/not be hidden depending on the Yes/No answers. 

Do I understand correctly that the white/uncoloured fields would come from the Assets table and the Yellow fields from the Asset Inspections table but would both still appear on the same layout?

I would prefer a list layout there though but I am working with the Enlightened theme for the first time and while it's visually appealing it is not so good at showing field outlines of empty fields in Browse or Preview modes, which has made designing the layouts less easy for me. Ideally I would have a list of the assets with the latest value showing.

Quote

The AssetInspections table would hold the "varying" fields. It would be "wide" in the sense that all the "varying" fields, for all asset types, would be there. Naturally, you would want to have a separate layout for each asset type (you could use a OnRecordLoad script trigger to automatically select the appropriate layout). 

Would a Tabbed layout not work in this situation? A single layout with a different Tab for each asset type?

 

Quote

Finally, a note about printing (or viewing) a list of data collected during an inspection: clearly, there's no problem putting all the fields that are common to all asset types in a single layout; likewise, there should be no problem summarizing them using summary fields. The question is how to show type-specific fields on a single layout. This question has three possible answers: (1) overlay several fields and hide them conditionally so that only the fields relevant to record's asset type are displayed; (2) use calculation fields to display the relevant fields' data in each column; (3) study a technique known as "Virtual List".

Would the Tabbed layout design also solve this issue?

[comment]

4 hours ago, SwissMac said:

When you say "for which you do no need to keep history" I assume you mean the fields for which varying values do not need to be kept, rather than fields which can be deleted once they are deactivated as being current assets? I need to keep all information, even deactivated assets, for a minimum of ten years.

I mean fields that can be modified without keeping their previous value. Nothing needs or should be deleted - you only mark an asset as deactivated.

 

4 hours ago, SwissMac said:

Would this all be happening on a single layout with fields displayed from the relevant tables? I

Probably not (at least not all of it). I have addressed only the data structure; now we are entering the issue of preferred workflow and building a UI to support it. The way I see it, most of the data entry work would be done using a form layout of the AssetInspections table. Since this table is child of both Assets and Inspections, it certainly can show fields from both its parent tables (as well as from its grandparent Clients table).

 

4 hours ago, SwissMac said:

Would a Tabbed layout not work in this situation? A single layout with a different Tab for each asset type?

Matter of preference.

 

4 hours ago, SwissMac said:

Would the Tabbed layout design also solve this issue?

No. A tab control will not show different panels for different records.

 

[SwissMac]

On 11/15/2021 at 7:26 AM, comment said:

So taking this, along with your self-described skill level into account, I would suggest you make do with the following 4 tables:

 

FM says i cannot have 2 relationship pathways so one of the tables need to have a TO. FM suggests AssetInspections. Not sure what I'm doing any more.

[comment]

What I posted is an Entity Relationship Diagram. To implement it in Filemaker's Relationships Graph, same tables need to have multiple TOs.

Suggested reading:
http://www.nightwingenterprises.com/Resources/approaches_to_graph_modeling_en.pdf