Discussions

 View Only
Expand all | Collapse all

Display field of most recent child record?

Warren Willits

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?

    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?

    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 05-10-2022 15:33
    Hate to bring back an old thread but the approach described works only with record IDs or can it be done with other fields as well?
    For example if I want the most recent item from the child table based on a date field is there a way to do so?

    ------------------------------
    Francesco Gallarotti
    ------------------------------



  • 13.  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!!!


  • 14.  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?


  • 15.  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.


  • 16.  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.


  • 17.  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.


  • 18.  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])




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

    Posted 06-27-2020 13:32
    Edited by Rick Putnam 06-27-2020 13:35
    This is a nice technique. Unfortunately, I found the description fairly confusing, so I'm going to restate it in a way my beginner brain understands. The following description assumes a relationship has been created between Parent and Child tables and you are on the Edit Relationship page:

    1. On the Parent side, create a Summary Field of type Maximum and relate it to the [Record ID#]*. Call it [Latest Child Id]. This field will capture the Record ID# of the most recent Child record related to a given Parent.
    2. On the Child side, create a Lookup Field that references the Parent's [Latest Child Id] (the one we just created in step 1). The name will default to [Parent - Latest Child Id]. This will be a field on each Child record referencing the Child [Record ID#] that the Parent sees as the latest one.
    3. Finally, on the Parent side, create a Summary Field of type Combined Text that references the field you want to display, say "Your Field", from the latest Child. Under the "Only summarize records where the following is true" section, set [Record ID#] equal to [Parent - Latest Child Id] (the lookup field created in step 2). This allows the Summary Field to filter out all but the last of the Child records. Call the new Summary Field [Latest Child "Your Field"].
    4. Display the Parent's new Summary Field [Latest Child "Your Field"] on reports and forms as called for in your requirements or use cases.

    Note wherever I've used "Child" or "Parent" in a field name substitute the names from your tables.

    * [Date Modified] might be a better way to capturing the "most recent record" depending on your use case.




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

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


  • 21.  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?   


  • 22.  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.   


  • 23.  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.


  • 24.  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]


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

    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.


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

    Posted 04-29-2020 14:04
    Hi All, 

    So this was a very helpful discussion thread and I have 99% of this working for me, however, I am unable to pull the most recent data point when its a "user" field.  Is there a reason I cannot "summarize" a user field?

    ------------------------------
    Brandon Davey
    ------------------------------



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

    Posted 04-29-2020 14:32
    Only a text type field can be summarized into a Combined Text Summary field.
    but np
    Create a text formula field called [User in text format]

    ToText([my userid field])

    Summarize that.

    Then convert it back to a User field on the Parent record with a formula user field.

    ToUser(ToText([My Combined text summary user field]))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------