Discussions

Expand all | Collapse all

Display field of most recent child record?

Warren Willits05-29-2019 19:29

  • 1.  Display field of most recent child record?

    Posted 06-05-2014 17:24

    Parent table: Computers

    I want a field in this table to reflect the most recent "status" that was entered for each child record in a Statuses table.

    I'm sorry -- I'm sure this has been answered somewhere, but I've tried about five different searches to see where the answer for this question would be.

    Thank you!



  • 2.  RE: Display field of most recent child record?

    Posted 06-05-2014 19:22
    Make a summary field of the maximum of the Record ID# of the child status table records called [Maximum Record ID# of status records]

    Make a new relationship where 1 Status is related to Many parents - ie a reverse relationship and the reference field at the right side will be that field [Maximum Record ID# of status records]

    Then just do a lookup of the Status from the Child to the Parent. but QuickBase will see it as a lookup from a parent to a child.


  • 3.  RE: Display field of most recent child record?

    Posted 03-30-2017 23:16
    Hi!

    My apologies for the very naive question but how/what is the syntax to get the maximum record ID#?
     I created a field and typed ' Max([CI-PN ID#]) ' under the formula field but the syntax seems to be incorrect.
     How do I get a max of a record?
    And more generally how can I get the max of a table or a column of a table?

    Thanks for your help


  • 4.  RE: Display field of most recent child record?

    Posted 03-31-2017 00:05
    :)
    You need to have a relationship first. If you need to have the Maximum of a whole table just to get it for some reason,, the you will need ot set up sort of a dummy table and enter one record into it.  It will be [Record ID#] of 1.

    Then on the details table where you want to know the Max, you will make a formula field called perhaps [Link to max record table (=1)] and make it a formula numeric field with a formula of 1.

    Then make a relationship where that 1 Max record table has many (in fact all) details records based on that link field used on the right hand side of the relationship.

    Then make a summary field on that relationship for the max record ID. There is a button to make a summary field on the left side of the relationship.

    Then do a lookup of that max field down to the child table and every child will know what the max record ID# is.


  • 5.  RE: Display field of most recent child record?

    Posted 03-31-2017 16:58
    Thanks!
    I realize it is much different from what I would have done on excel/matlab..A whole new philosophy to embrace ;-)
    I have done my homework and managed to retrieve the latest status which was my initial intend. Thanks a lot for the explanation!


  • 6.  RE: Display field of most recent child record?

    Posted 03-31-2017 17:20
    Yes, I know that it seems strange to have to build a whole table, but that is what we do.


  • 7.  RE: Display field of most recent child record?

    Top
    Contributor
    Posted 03-31-2017 17:56
    Don't despair Marlene I am a long time Matlab user and we are going to bring more functional paradigms to QuickBase and use JavaScript for everything starting with replacing the formula language with script.


  • 8.  RE: Display field of most recent child record?

    Posted 03-31-2017 18:12
    Hi Danimal! I am actually going right now through your pasties #314 (which brings a functionnality that I expected to be built-in but is apparently not). I may have to come back to you about that too.
    Anyway thanks to both of you for sharing your expertise. On-line support told me for both questions that is was just impossible to do in quickbase. I am glad to see that there are ways around..


  • 9.  RE: Display field of most recent child record?

    Top
    Contributor
    Posted 03-31-2017 18:16
    Online support uses some coded language. When they say something is "impossible" they mean that you should use script.


  • 10.  RE: Display field of most recent child record?

    Posted 07-29-2017 00:17
    Is there any way to pull the most recent child record using maximum date instead of maximum record ID#?  In my application, some child records were created out of sequence with more recently created entries having older dates.  I imagine that using Maximum Record ID# works because then it ties to the record number on the reverse lookup.  I can't figure out how to make it work with date.. any thoughts?


  • 11.  RE: Display field of most recent child record?

    Posted 07-29-2017 00:37
    No problem

    Summarize max date.
    Lookup to child

    Summarize max record ID subject to the filter that date = max date.


  • 12.  RE: Display field of most recent child record?

    Posted 06-05-2014 21:29
    Wow, so impressive! I would NOT have thought of this. Thanks!!!


  • 13.  RE: Display field of most recent child record?

    Posted 04-19-2016 02:45
    I tried this and I'm lost. I built a new simple app just to test this, but can't seem to get the Parents to display the highest numbered related status. Is there any more detail for this available?


  • 14.  RE: Display field of most recent child record?

    Posted 04-19-2016 02:56
    Contact me via the information in my profile and I will have a quick look at your test app.


  • 15.  RE: Display field of most recent child record?

    Posted 04-19-2016 04:11
    Thanks for the quick response. I found another response you wrote to a similar question, and that had enough for me to get it working.


  • 16.  RE: Display field of most recent child record?

    Posted 05-29-2019 15:03
    I know that this is a very old post but I have a similar but more complex issue. I have a fact table that captures aircraft base locations on a monthly basis (the new monthly data is appended to the table). The table captures many aircraft and their locations every month, but I am trying to pull out only the latest location for each aircraft, which means that I'm only looking for the last input for each aircraft.


  • 17.  RE: Display field of most recent child record?

    Posted 05-29-2019 15:54
    There is a new  type of summary field which makes this much easier now.

    1. Make a summary Maximum of the [Record ID#] of the locations table.  call it [Record ID# of most recent location]

    2. Look that up from the Aircraft down to Locations.

    3. Make a new summary field of Combined text type to summarize the locations, subject to the filer that [Record ID#] = [[Record ID# of most recent location]

    That will be the last location now up on the Parent Aircraft record.

    That field will have a bubble-like format.

    If that bothers, you, you can make a new formula text field field with the formula

    ToText([my combined text summary field of the most recent location])




  • 18.  RE: Display field of most recent child record?

    Posted 05-29-2019 19:29
    OK, thanks I will give it a try.


  • 19.  RE: Display field of most recent child record?

    Posted 05-31-2019 17:34
    OK, so I have run into another issue. When I try to create a Summary Field on my Parent Table (Aircraft table) from my Child Table (Location table), I am getting an error that states "you cannot create a summary field using a relationship where the reference field is a lookup field". The issue is that the foreign key in my Child Table (Location table) is set up as a look-up from the Parent Table (Aircraft table) so that the user doesn't have to remember the detailed Aircraft codes when inputting a Location. Is there any way around this or do I have to create a new input that requires the user to manually type in the Aircraft code every time they input the Location data?   


  • 20.  RE: Display field of most recent child record?

    Posted 06-02-2019 01:00
    Warren, can you describe the relationship between Aircraft and Locations?  What is the Key field of Aircraft?  Is it a simple relationships where 1 Aircraft has many Locations?

    But if you have a legitimate reason for the setup you have, then the solution will be to have a mirror field which is maintained which has a duplicate of the related Aircraft field, so that it is not a lookup field.

    Then typically I set up a save table to table copy to copy the locations table into itself merging on record ID#, and updating that mirror field.

    Then I have an Automation fire to run that saved import whenever locations are added or edited and the lookup field does not match the scalar mirror field.   


  • 21.  RE: Display field of most recent child record?

    Posted 06-03-2019 16:32
    Yes, it is a single aircraft to many locations relationship. We have a dimension table with all the aircraft information and a fact table with all the various airport locations that the aircraft is based each month. The Aircraft table is basically a static list while the locations table is being updated every month.

    I did try creating a new text formula field where I basically copied the Aircraft code from the Aircraft lookup input, but QuickBase still gave me an error stating that it was a Lookup field.


  • 22.  RE: Display field of most recent child record?

    Posted 06-03-2019 16:46
    Can you tell me the Key field of the Aircraft table and the where you get the data for the field likely called [Related Aircraft]


  • 23.  RE: Display field of most recent child record?

    Bronze
    Contributor
    Posted 05-31-2019 18:23
    A possible work around, while not entirely clean... create a formula field which will have [Foreign Key] as the contents... use this new formula field to make the summary field. Same values, but technically not from the parent table to begin with.