Setting up relationships

[Dave123]

Hello, I'm needing help with creating the relationships for the requirements shown below. (For each table, the user should be able to add one or more notes. Should this be a single table or should a Notes table be created for each corresponding table?) Thanks for any help with getting this started.
 

Person 
1.    Person record is unique. (Note:  There is no unique identifier (such as SSN) that the office that manages this information will enter.)
2.    Start date, End date and notes are kept in a historical record.  (Notes will be a separate table.)
3.    Need to give them the ability to deactivate a person record.
a.    This would deactivate all records without an end date (**Warn user before proceeding.**)
 
  
Person History
 There should not be more than one active history record at a time.
1.    The screen should show the most recent information.
 
 
Applications
 1.    Person can have multiple applications but only one application can be active at a time.
2.    Application record would be tied to documents, appointments and person records.
3.    User needs to have the ability to add appointments based on the application.  
a.    User should not be able to add appointments to an end-dated application.
4.    User needs to have the ability to add documents based on the application.
a.    User should not be able to add documents to an end-dated application.
 
1.    If an application is end-dated, all corresponding appointment records should be end-dated.
  
Appointment
 1.    A person can have multiple appointments over a period of time with one application. 
2.    Should not be able to add an appointment if there is an active one for the same county
3.    If an application is end-dated, all corresponding appointments should be end-dated. (**Warn user before proceeding**)
4.    User needs to have the ability to add documents based on the appointment.
a.    User should not be able to add documents to an end-dated appointment.
 
Documents
 1.    Documents can only be pdfs, no other document formats can be uploaded.
2.    Allow user to edit the document-based dictionaries
  
 
Notes
1.    Pop-up for user to enter the note
2.    Show a list of notes on the screen that pertain to the current record
3.    Show a list of notes on the screen that pertain to all history records
4.    User can see all of the notes that were added as well as the specific note added to the corresponding section.  i.e. Person, Application, Appointment.

 

[Søren Dyhr]

I Would like to help out.here… allow me some time to make a template, upon the info given here.

—sd

[Dave123]

On 4/15/2024 at 3:32 AM, Søren Dyhr said:

I Would like to help out.here… allow me some time to make a template, upon the info given here.

—sd

Thank you. I apprecate this so much

[Søren Dyhr]

Alright here is my initial suggestion, what i still are struggling with is the validations, instated to prevent more than one application at a time ... but I'm getting there .... then is there a question mentioned in the relations graph.

Please investigate the template, and turn back as soon as you should wish - for further explanations if required?

--sd

Dave123.fmp12

[Dave123]

Thank you. I will take a look at your suggestions. I appreciate you taking time to look at this for me,

[Søren Dyhr]

Dave1234.fmp12

[Dave123]

Hello Soren, thank you for the update. I am looking at this now. I appreciate you taking the time

[Søren Dyhr]

1 hour ago, Dave123 said:

Hello Soren, thank you for the update. I am looking at this now. I appreciate you taking the time

Yes I've forgotten how I could exploit "active row state" in the lefthand side portal of Person ... but else must yet more work, be done on the validation of each date span, to prevent reaching into next appointment date span, it requires you somehow makes the validation look at several records in the portal. I have thought about using Get(layoutobjectattribute) ... if someone else have som ideas here are they welcome to chip in.

But perhaps wouldn't it be a requirement at all, but it often are better to play safe, if someone else as user at some point tries to outsmart us?

Then to the structure, there might be missing a joinfile or two in the documents as well as in the notes? Allowing them to continue from one appointment to the next, and perhaps further on to one succeeding the second one. 

Yet another question is - are the historical tracking some sort of audit logging?

--sd

Edited April 17 by Søren Dyhr