Creating a Button That Goes to Table with only Linked Records

David_Irving
1 yr ago
26replies

I have been searching endlessly and can't find the correct formula.

I want to create a button that when you click on it, it will display the child table but only the linked records to that specific record I am viewing in the parent table.

Example (Parent Table: Clients) ***Button is View Full Chronos***

Then when clicking on it, it will bring up the Child Table: Chronos and only display records from Client ID 10970 for example:

Any help is greatly appreciated.

- John_Halls
- 1 yr ago
- Reported - view
Hi David

Have a look at this post I put up about Virtual Lists

https://forum.ninox.com/t/m1hrzt8

It's not exactly what you describe above, but the end result is essentially the same.

Hope this helps

Regards John
- David_Irving
- 1 yr ago
- Reported - view
Sorry John Halls I had difficulty understanding the applicability to my specific problem. I want this to be for every single client record as every single client record has "chrono" records which are stored in a child subtable. I just want a way where a user can simply click on a button within a client record to view only that client's chronos without having to open the whole child substable and search for that client either by name or client ID.
- John_Halls
  
  1 yr ago
  
  Reported - view
  David Irving Did you try it because that's exactly what it will do?
- Fred
- 1 yr ago
- Reported - view
Since you have a Chronos view, why do you need to open the Chronos table at all? The view shows them the same information as they would by opening the table separately.
- John_Halls
  
  1 yr ago
  
  Reported - view
  Fred I do as David is asking across my own solution. I can have a few hundred child records per parent and there are times when I want to see more than just six of them at a time. It then also gives me the option to search and sort within that subset. This is something that FileMaker does out of the box with its GTRR (Go To Related Record) command.
- John_Halls
- 1 yr ago
- Reported - view
Hi David

Here's an alternative way.

Add a new field to your child, lets call it Subset

Add a new child view (let's call it By Client) that matches the existing view but swapping out Client ID for Subset. Rename the column Client ID.

Set a filter on Subset to !=null (which means not empty)

Add a button on the client table with the following code

for a in select Chronos where Subset != null do a.(Subset := null) end; for b in Chronos do b.(Subset := b.'Client ID') end; openTable("Chronos", "By Client")

I tested this on a db with hundreds of child records per parent and it was a bit slow so I played with speeding this up using do as server but with mixed results. I also tried setting the binding of Subset to Per record in memory, again with mixed results but this would be the better option as it makes the solution suitable in a multi-user environment.

Let me know how you get on.

Regards John
- John_Halls
- 1 yr ago
- Reported - view
Got the do as server version to work consistently.

do as server for a in select Chronos where Subset != null do a.(Subset := null) end; for b in Chronos do b.(Subset := b.'Client ID') end end; sleep(1000); openTable("Chronos", "By Client")

It wasn't waiting to finish the loops before opening the table. Adding a sleep command allows it to catch up first and then open the table. 1000 = 1 second. You may not need to to wait that long in your solution, if at all.

Regards John
- Alain_Fontaine
  
  1 yr ago
  
  Reported - view
  John Halls I did not test this on a large database, but it is possible that the variant below could be somewhat faster:
  
  let cId := ClientId; (select Chronos)[Subset].(Subset := null); Chronos.(Subset := cId); openTable("Chronos", "ByClient")
- John_Halls
  
  1 yr ago
  
  Reported - view
  Alain Fontaine Marginally faster but far, far better code. Thank you.
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls
  
  Hi John you have the example to Share if is possible look interesting.
- John_Halls
  
  1 yr ago
  
  Reported - view
  Rafael It’s in my own system. I can put together an example over the next few days.
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls Thanks John
- Alain_Fontaine
  
  1 yr ago
  
  Reported - view
  John Halls Here is the minimum mockup I made to test the idea.
  
  Test Chronos.ninox
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  Alain Fontaine Thanks
- John_Halls
- 1 yr ago
- Reported - view
Alain Fontaine I'd just done the same, may as well include it too!

Rainfall.ninox
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls Thanks John
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls Alain Fontaine
  
  I try to implement it on my DB, but " This formula may not modify Data" some problems here.
  
  let a := Cod_RQ; (select Renglones)[Subset != null].(Subset := null); Renglones_RQ.(Subset := a); openTable("Renglones", "By RQ")
  
  Cod_RQ (Cod Requisitions) and Renglones one to many, One Requisitions have many Renglones.
  
  The Renglones_RQ is on Requisitions table 1:N to renglones
  
  Thanks
- John_Halls
  
  1 yr ago
  
  Reported - view
  Rafael Your code needs to go in a button.
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls Hi John yes is on Button
- John_Halls
  
  1 yr ago
  
  Reported - view
  Rafael Shouldn't it be
  
  let a := Cod_RQ; (select Renglones)[Subset != null].(Subset := null); Renglones.(Subset := a); openTable("Renglones", "By RQ")
  
  Changing
  
  Renglones_RQ.(Subset := a);
  
  to
  
  Renglones.(Subset := a);
  
  Regards John
- Rafael Sanchis
  
  Rafael_Sanchis
  
  1 yr ago
  
  Reported - view
  John Halls Hi John
  
  I send you small part of my DB, the code work, but witn little change, I need to place the !=null in the Subset on "By RQ".
  
  But the same code don't work in the main DB
  
  Procurement.ninox
- John_Halls
  
  1 yr ago
  
  Reported - view
  Rafael Thanks Rafael
  
  Works like a charm!
  
  Regards John
- John_Halls
- 1 yr ago
- Reported - view
David Irving Hi David. Did we answer your question in the end?

Regards John
- David_Irving
- 1 yr ago
- Reported - view
Sorry John Halls I was dealing with COVID and have just got back to this. I am going to try some of these solutions and get back to you. Thanks so much!
- John_Halls
  
  1 yr ago
  
  Reported - view
  David Irving Hope you feel better soon.

Content aside

1 yr agoLast active
26Replies
560Views
5 Following

Creating a Button That Goes to Table with only Linked Records

26 replies

Content aside

Overview