How to display ONLY the current status in a one-to-many relationship on the "parent" table?

[dmontano]

Hi all,

I can not figure out how to do this: 

"One" PROJECT will have "many" different STATUSES over time.

I built two tables (one table for project and one table for status in a one-to-many manner related by "project_id"). I built a status portal on project layout: this works just fine for recording history of statuses for a given project. But…how can I display ONLY the most recent status based on date on the project layout?

I did build the status table with additional fields to hold the status start date AND status end date. 

I believe the answer lies in a calculated field in the STATUS table that somehow displays the most RECENT status based on dates of related statuses. This is the calculation I wrote that at least puts in the value I want - except it only works when "status_date_effective_end" is empty. I know it is not correct, but sharing to show I am trying! lol.

If ( IsEmpty ( status_date_effective_end ) ; status__status_state__status_state::status_state_name ; "" )

If a calculation is an appropriate way to display what I am trying to accomplish - can someone lend a hand?

 

 

 

[comment]

I don't think you need an end date for a status - unless a project can have more than one status at the same time, or if there can be a gap between statuses. 

To display the most recent status on a layout of Projects, you can:

• Sort the related records by start date, descending,  and place the related status field onto the layout (make it non-enterable to prevent accidental modification);
• Use a one row portal sorted by start date, descending;
• Define a calculation field in the projects table, using the Last() function.

 

Edited July 8 by comment

[Søren Dyhr]

9 hours ago, dmontano said:

I built two tables (one table for project and one table for status in a one-to-many manner related by "project_id"). I built a status portal on project layout: this works just fine for recording history of statuses for a given project. But…how can I display ONLY the most recent status based on date on the project layout?

I'm wondering why this is broken out in another table, is there a timestamp shown as well - for each obtained status, and is the flow thru each of those always the same, or is it the sole purpose of portalising?

Perhaps this template can give you some ideas?

--sd

project.fmp12

-----------------------

I have discovered an error in the previous approach, it's putting the timestamps into wrong portal rows:  

project2.fmp12

Edited July 8 by Søren Dyhr

[dmontano]

8 hours ago, comment said:

I don't think you need an end date for a status - unless a project can have more than one status at the same time, or if there can be a gap between statuses. 

A project will NOT have more than one status at a time. However, there can be GAPS - if I understand you correctly. I am attaching a screenshot of a make-believe progression of statuses.

9 hours ago, comment said:

To display the most recent status on a layout of Projects, you can:

• Sort the related records by start date, descending,  and place the related status field onto the layout (make it non-enterable to prevent accidental modification);
• Use a one row portal sorted by start date, descending;
• Define a calculation field in the projects table, using the Last() function.

Thank you Comment. I can see a bit better as to how to go about this. I think I understand the first and second bullet, and will need to spend time on the third bullet (I have never used the "Last" function but a bit of reading tells me it returns the literal "last" record. Your instruction to "sort" gives a hint that the sort operation prepares the list of records for the Last function to grab what is needed for the most current status value.

I may have this understood poorly, but will give this a go.

[comment]

12 minutes ago, dmontano said:

However, there can be GAPS - if I understand you correctly. I am attaching a screenshot of a make-believe progression of statuses.

I don't see any gaps in the example. Every status begins on the same date that the previous status ended. That means you have unnecessary duplication of data, and this exposes you to update anomaly.

 

Edited July 8 by comment

[dmontano]

5 hours ago, Søren Dyhr said:

I'm wondering why this is broken out in another table,

Hi Soren,

I have two tables as I need to maintain the history of status as a Project progresses. I need to record the Status value and the date of each status change. Since a Project can (over time) have the same status value (for example "In Process" shown in screenshot attached above) but at different date intervals I thought two tables was necessary.

Also, I downloaded your second attachment and you have included basically everything that I am trying to build at first glance. I reviewed your scripts and they are more advanced than where I am at now - but will continue to learn from it.

Thank you

[dmontano]

8 minutes ago, comment said:

I don't see any gaps in the example. Every status begins on the same date that the previous status ended. That means you have unnecessary duplication of data, and this exposes you to update anomaly.

I wholeheartedly admit that this is an area I have struggled with.

I thought I needed an "end date". I will remove it and see if I discover some requirement for it.

After looking at Soren's file, I noticed he used TimeStamp in order to differentiate the fact that statuses CAN be changed multiple times a day. So, my use of just a Date is insufficient actually. I will change this to TimeStamp to allow the sort to accurately reflect the progression during any particular day.

As for the two tables - I am stumped on both your view and Soren's view that perhaps two tables are not needed. If I highlight the fact that I need to store the "progression" of statuses that a particular project has gone through, in other words, the history of a Projects status cycle — does that make a difference in your view regarding two tables? I still believe I need two tables.

As always, I do appreciate both you and Soren's help.

[comment]

9 minutes ago, dmontano said:

I am stumped on both your view and Soren's view that perhaps two tables are not needed.

That is not my view at all. If a project changes status over time, and you want to keep a history of these changes, then you should have a child table to record them. At least that is the default solution. If you want to employ a different one, you will have to give up some functionality. 

 

 

[dmontano]

36 minutes ago, comment said:

That is not my view at all.

My mistake, I re-read the thread and somehow lumped you into the same speculation Soren shared about two tables.

Thanks again!

[Søren Dyhr]

1 hour ago, dmontano said:

My mistake, I re-read the thread and somehow lumped you into the same speculation Soren shared about two tables.

The reason why .... I had something like this in mind, instead:

https://www.nightwingenterprises.com/demos2014/demo1306.html

But then is the privileges set to prevent deleting any record and a status of "cancelled" are likely to be part of choices of status, since all changes to a record is stored in a large text field in each record.

But I'm glad the template i made helped you, approaching the solution as such!

--sd

[bcooney]

I update a projects status using a script that creates the status history record, sets an end date in any open status record and sets the current status type Id in the project record. Since this process creates and edits multiple records, it’s done using a transaction. 

[Søren Dyhr]

4 hours ago, bcooney said:

Since this process creates and edits multiple records, it’s done using a transaction. 

....and it takes care of accidentally deletions of records, as well?

--sd

[Søren Dyhr]

17 hours ago, dmontano said:

I reviewed your scripts and they are more advanced than where I am at now - but will continue to learn from it.

Would you like to have me commenting out the script steps, or du you get what I'm doing?

--sd

[bcooney]

Deletions of what? Status history records? That would be controlled by security. 

[Søren Dyhr]

9 hours ago, bcooney said:

Deletions of what? Status history records? That would be controlled by security. 

Aren't you talking about the same kind of transactions as Corn, does it in this video?:

https://youtu.be/TrIvDnSPNV0?si=q5lmFqpTzM-AiXLc

--sd

[bcooney]

Yes, I work with corn. These are the newly supported script steps released in v19.6. 

[comment]

17 minutes ago, bcooney said:

I work with corn.

"What a coincidence, so do I!"

Edited July 10 by comment