I am building a custom database for a law firm. I am rapidly approaching 60 tables, as it records everything you could possibly imagine related to plaintiffs, defendants, judges, courtrooms, filing dates, home addresses, business addresses, injuries, settlements, etc.

 

I was about done with the database, when they added a new wrinkle. For every single field in the database, they want to record the source of where the data came from. (Where did the first name come from? Where did the last name come from? Where did the address come from?) They want to be able to interact with this information during data entry, as well as having it show up on reports.

 

Short of adding an extra field for every piece of data in every table, does anyone have a good way to go about this?

 

I’ve experimented with creating a script that is able to create unique values in an unrelated table for every source value while doing data entry (if it doesn’t already exist) using a custom dialog, but then I can’t figure out a way to get those values to show up again next to each field when doing reporting.

 

Any help would be appreciated.

I would suggest to have one single table called "Sources" to record "sources" of each field. So you may have below fields on that table:

 

- ID

- Field name

- fk_RecordID

- Source

- Created by user

- Creation Timestamp

- etc.

 

Then use script step get(activefieldname) to determine which field is active. This is the field that you want to assign a source to it.  By having the name of active field, you can create a new record in the "Sources" table and set the Field name by result of get(activefieldname) and set fk_RecordID to the target record ID from your table. 

 

The Source table can be either related to your target tables or not.

 

This way you can save one record per field of any table in this table called "Sources" and have it added to your reports later on. 

The logic is some how same as  audit logging. 

 

 

 

I understand the creation process to build the records in the table, but how do I get the data back out on a report?

I will I be able to link back to the source that was entered? For instance, how can I get the report to list the source entered for First Name in the Employees table now that the source is stored in an unrelated table?

You can make a relationship between the Sources table and your target table using the fk_RecordId = RecordId. 

 

Then you will be able to show related fields from Sources table on a report layout based on your target table.

I guess I'm a bit confused.

If I have a table called Contacts with the following information:

UserID: 1

FirstName: Ben

LastName: Hill

---

UserID: 2

FirstName: Don

LastName: Jones

 

I can produce a record in another table called Sources that would then look like this:

 ID: 1

Field name: FirstName

fk_RecordID: 1

Source: I found their first name by searching the employee database.

---

 ID: 2

Field name: LastName

fk_RecordID: 1

Source: The last name was supplied by the employer.

---

 ID: 3

Field name: FirstName

fk_RecordID: 2

Source: The source is yet unknown

 

What still confuses me is how on a report to connect this information on a report.

On the report it would need to look something like this:

 

Field               Value               Source

UserID            1                             

FirstName       Ben               I found their first name by searching the employee database.

LastName       Hill                 The last name was supplied by the employer.

---

UserID              2

FirstName      Don                  The source is yet unknown

LastName      Johnson

I think it is easier for us to have a real sample file, Please see attached:

Sources on report.fmp12

I see.

Lots of 1 row filtered portals.

Is there any possible way to do this without Portals?

29 minutes ago, bcooper said:

Is there any possible way to do this without Portals?

Yes, put the filter applied to portal in relationships and then show the appropriate field on the layout. see attached:

Sources on report- No portal.fmp12

I see how it would work.

Unfortunately, that would force me to create a duplicate of every singe field in my database.

Okay, lets see what other folks would suggest. mean while I will think about any other solutions and will let you know if came across any ideas.

Hi bcooper and welcome to the FM Forums,

I sent you a Private Message, please read it.

Thank You,

Lee