Link Records for Grouping in Same Report

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
I have a table that has  'Material Number' and 'Linked Material Number'.  there could be 5 records with different Material Numbers, but all have the same  'Linked Material Number'.
There can be 'Material Number' records that the 'Linked Material Number' is empty, no links.

I need a report  that will list all the records that the 'Linked Material Number'  is the same in a group where at least one of them have the 'Due Date' populated.  If none of the linked records have the 'Due Date' populated,  none of them would show up on the report. It would also need to list any 'Material Number'  without a 'Linked Material Number'  if it had a 'Due Date' populated.  

Is this possible?

Thanks for any insight!

Jeanne
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Do you have any relationships, or is this a single table?  Or is the table related to itself, perhaps?
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb
the table 'COGS' is the parent table and has relationships to child tables.  but for this report i do not anything from any of the childern, all needed fields are in COGS.

thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
You would need a relationship to a new table called Linked Material Number.  That would have a relationship down to the detail records and it would be able to summarize the information you need and look it up back down to the detail records.

The key field of that table would need to be set to be the field Linked Material number.

To initially populate that Table you would need to make a summary report on Linked material number to get all the unique values and then copy them, across to the new table.

If you go to add a record and the parent is not there, then you would need to create a new Linked material record for it.  Alternatively if you want to continue to be able to just free form in the data for Linked Material, there is a way with using an Action to auto create the parent record.

So this is kind of tricky to set up if you are new to Quick Base.  You may need some one on one help with this or several iteration on this forum here.
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb
Think I understand what you are saying, but we are adding to the parent table 'COGS' daily, would i want in turn add any new  linked material numbers to the 'linked material number' table? I would never have anything added to the new 'linked material number' that wasn't in the parent table.

thank you for your help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Yes, so the Parent records in the Linked Materials table could be created automatically by an Action.
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb
But i need to be able to edit the records in the COGS table, will this allow me to do that?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
If you are asking if you can Grid Edit the Material Number records while sitting on a COGS record, yes you would be able to do that if you use an Action.

But there is a tricky part here in that the relationship to the Linked Material table will need to be via a formula field which mirrors the value of the Linked material input field. That way the user can just free form data entry the Linked Material data and the Action will create the missing parent Linked Material Record if it is needed.  I don't think that the Grid Edit would let you have that pop up to Add new Linked Material if it was not there already.