Jump to content

Need to create a place to store a "source" for every field of data


This topic is 2270 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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. 

 

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2270 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.