Creating a new record in a linked table using code
I have 3 tables linked to create a many to many relationship.
The relationships look like this: [Locations] <--- [Location Tracking] ---> [Tracking]
Multiple tracking records and be related to every location, and multiple locations can be related to every tracked record. I find the process of having to create a new record in Location Tracking from the Tracking table to be cumbersome, so would like to automatically create records in Location Tracking using code when certain Tracking activities are chosen. For example, if "Work" is chosen for Tracking, then "Office" would be automatically populated from the Locations table via the Locations Tracking table.
So far I have been able to create a new record in Location Tracking, but I'm not able to figure out how to link the "Work" Tracking record that has been created to the "Office" record in Locations.
Here is the code I've been playing with, which works to create a record but won't populate the Ids in Location Tracking.
let trackID := Id;
let locID := 6;
let newRec := (create 'Location Tracking');
newRec.Tracking.(Id := trackID);
newRec.Locations.(Id := locID)
Any suggestions?
Thanks,
Barry
18 replies
-
You are almost there... Two things:
- use the "record()" function to create a record handle for the desired record in "Locations";
- set the reference field itself - not the ID of the referenced record - to the handle of the record you want to link.
let trackID := Id;let newRec := (create 'Location Tracking');newRec.(Tracking := trackID);newRec.(Locations := record(Locations,6)) -
That's awesome! Thank you so much.
-
@Alain
I have followed your example for my case but have have not had complete success.
I am converting an FMPro solution to Ninox
I have 2 Tables:
Routes
Ride Log
‘Ride Log’ is linked to Routes as 1:N
In Routes I use a number field Route_ID to link to the ‘Ride Log’ table number field ‘Route ID’ ( yeah I know the field name is subtle )
Use case:
In the Routes table I find the route I want by using the number field ‘Route ID Match’
When creating a new record in ‘Ride Log’ I need to do 2 things with that number:
Set field ‘Route ID’ in ‘Ride Log’ to ‘Route ID Match’
Set the link reference field Route_ID in Routes to ‘Route ID Match’
I have been trying various versions of your code to accomplish:
let xlnk := 'Route ID Match';
let newRec := (create 'Ride Log');
newRec.('Route ID' := xlnk);
newRec.Routes.(Route_ID = xlnk);
popupRecord(newRec)
The ‘Route ID’ field in ‘Ride Log’ is set but not the link reference Routes ‘Route_ID’
I am able to update the link reference “manually” at the ‘Ride Log’ table level by selecting the appropriate record and using the Bulk edit feature and the following code:
let xlnk :='Route ID'
first (select Routes[Route_ID=xlnk])
By the way, to convert the FMPro tables initially to Ninox I used the bulk edit bit above - advice from another thread traceable to you!
The community is fortunate and benefits from your expertise - thank you!
Kind regards
-
Alain Fontaine
reposting with correct @mention
I have followed your example for my case but have have not had complete success.
I am converting an FMPro solution to Ninox
I have 2 Tables:
Routes
Ride Log
‘Ride Log’ is linked to Routes as 1:N
In Routes I use a number field Route_ID to link to the ‘Ride Log’ table number field ‘Route ID’ ( yeah I know the field name is subtle )
Use case:
In the Routes table I find the route I want by using the number field ‘Route ID Match’
When creating a new record in ‘Ride Log’ I need to do 2 things with that number:
Set field ‘Route ID’ in ‘Ride Log’ to ‘Route ID Match’
Set the link reference field Route_ID in Routes to ‘Route ID Match’
I have been trying various versions of your code to accomplish:
let xlnk := 'Route ID Match';
let newRec := (create 'Ride Log');
newRec.('Route ID' := xlnk);
newRec.Routes.(Route_ID = xlnk);
popupRecord(newRec)
The ‘Route ID’ field in ‘Ride Log’ is set but not the link reference Routes ‘Route_ID’
I am able to update the link reference “manually” at the ‘Ride Log’ table level by selecting the appropriate record and using the Bulk edit feature and the following code:
let xlnk :='Route ID'
first (select Routes[Route_ID=xlnk])
By the way, to convert the FMPro tables initially to Ninox I used the bulk edit bit above - advice from another thread traceable to you!
The community is fortunate and benefits from your expertise - thank you!
Kind regards
-
spinner. 7580 said:
When creating a new record in ‘Ride Log’ I need to do 2 things with that number:
Set field ‘Route ID’ in ‘Ride Log’ to ‘Route ID Match’
Set the link reference field Route_ID in Routes to ‘Route ID Match’In Ninox you don’t need a “key” field to link records between tables. But to continue with your method you can try updating your code to:
let t := this; let newRec := (create 'Ride Log'); newRec.( 'Route ID' := t.'Route ID Match'); Routes := t) ) popupRecord(newRec)Line 1 I changed to use the “this” command, which is a simple way of saying to Ninox gather all of the data for this current record.
Line 4 shows what you can do with with the new variable “t”. We can now pull up any field from the current record.
Line 5 shows how to set reference fields in Ninox. You just need to point it to a record and Ninox will make the link. No need for a “key” field like in other DBs. Which means you can leave out line 4 if you stick with Ninox.
-
Fred
Fred said:
In Ninox you don’t need a “key” field to link records between tables. But to continue with your method you can try updating your code toHi Fred - thank you very much for the feedback.
Line 5 didn't work but all is not lost.
As an FMPrpo refugee I'm having a bit of trouble conceptualizing the way that Ninox relates table to table vs table record to table record.
Lines' 1 thru 4 worked as expected and the results give me the desired results. But I don't fully understand.... my intent for line 5 was to update the Routes table via the Ride Log link reference. The intent for line 5 was to automate the bulk edit function to update the Ride Log link.
In the attached pic you can see the 'Ride Log link' view only shows 1 entry. Whereas the Ride History view shows 2 entries for the route 1035. There is only 1 route named Route_ID in the Routes table (Ride Log link 1:N)
Performing a bulk edit function or "Add existing record" results in a Route_ID entry for both records in the Route History view. ('Route ID' exists in the Ride Log table)
I'm fine moving forward but am confused as to where that second Route_ID really exists....
Kind regards
-
-
spinner. 7580 said:
Line 5 didn't work but all is not lost.Question: in what table does this button exist? I assumed it was in the Routes table, but since it doesn't work I guess not.
I was making some assumptions about your DB structure.
1) You have tables called Routes and Ride Log.
2) In Ride Log you have a reference field to Routes. Where if you opened a record in Ride Log you will see a field called Routes (or whatever you named it) and you can only select 1 record from Routes. If you see a table view called Routes with the Add existing record and Create record at the bottom then you have made the relationship in the wrong direction.
In Ninox you make the relationship from the many side, which would be Ride Log.
-
Fred
Fred said:
In Ninox you make the relationship from the many side, which would be Ride Log.Hi Fred -
I was able to make "line 5" (now line 6 see below) work. It was a matter of syntax on my end and referring to the correct table. The button is in the Routes table.
My coding may not be very elegant compared to yours but this is what I came up with and it works:
A little more background for you. Yes only 2 tables as you stated.
Routes has 1 each route. Ride Log has many entries and "relationally" shows how many times I've ridden a particular route etc.
When addng a new ride I start in the Routes table. I know the mileage of the just completed ride.
For example if I did a 53.2 mile ride I enter 53 in a field called Route Base Miles, I get a table with all of the routes that are equal to 53 and less than 54. A table displays, each line has the Route ID and the the Route Description. The full route description generally won't display completely since the table only shows 1 line.
I make an educated guess about one of the listed routes. I enter the route ID into the 'Route ID Match' field and the complete route displays in a text field. If its the right one I hit the to button create a new record in Ride Log and fills in the Route ID as well as making the "link" there and then I enter the other ride stats for that new Ride Log record.
Back to the code -- when I intially entered the 'Route ID Match' value into Routes_go I wouldn't see the correct Route_ID from the Routes table. I determined that since the Ninox ID and my routes do not correspond, i.e., over the years I may have skipped a number or deleted a record etc. So, that's what line 3 is about -- to grab the Ninox ID of the route referred to in 'Route ID Match'....
Thanks for menitoning where to make the relation in Ninox. Here is the relation I made in the Routes table:
Everthing works as needed. BUT in the Routes table I do see the table ref to Ride Log with the 'Add existing' / 'Create' buttons... and in the Ride Log table I see the single field with the Unlink button.....bummer! Should I re-plumb everything?!? The link arrow pic looks to me like 1:many (Routes:Ride Log) but maybe I'm database dyslexic...
Thank you again Fred for your counsel!
-
-
spinner. 7580 said:
For example if I did a 53.2 mile ride I enter 53 in a field called Route Base Miles, I get a table with all of the routes that are equal to 53 and less than 54.So you are in the Routes table when you type in the field Route Base Miles? Are you in a new record or in an existing record?
What table are you talking about?
If you don’t mind, can you upload a sample of your DB?
-
Fred
Yes, I am in the Routes table and in an existing record when I type in 'Route Base Miles' ( I don't try to be in a specific record when l type in 'Route Base Miles').
Upon entry of 'Route Base Mile' I get a view (I said table but view is correct) displaying routes with matching mileage in a range (mentioned).
The displayed view will likely have multiple route entries based on 'Route Base Mile' mileage range. The columns displayed in the view are Route_ID / Times ridden / Base Miles / Route create / Route Description.
In order to see a complete route description in a text field I enter the Route_ID value into 'Route ID Match' for the likely route candidate from the 'Route Base Mile' view. I may have to enter a few Route_IDs in order to identify the correct route.
Again - I'm still in Routes table at this point. ( I could be in any existing record )
With the correct route description identified based on 'Route ID Match' -- I hit a button that:
- creates a new record in Ride Log
- sets Routes_go "link" with the record Id from the Routes table of the route record in question
- sets 'Route ID' field in Ride Log with the value of 'Route ID match' in the Routes table
- performs popup record
The button process ends with the newly created record displayed in Ride Log with Route ID field populated and the record is ready for input of the ride stats for the ride.
Not sure of how to upload DB - to you privately or posted online?
-
-
spinner. 7580 said:
Not sure of how to upload DB - to you privately or posted online?If you are using the App with a local/iCloud DB then click on the upper right of the DB icon and select Save archive as..
If you are using a browser then you have to have the Professional level to save a manual backup.
If you have a cloud based DB, you can use the App to export the whole DB as a ninox file.
- Open the DB
- Select the DB name from the left side
- Click on the Export data button
- Select Ninox as the option
- Type an appropriate name and save
Then make a new response then click on the paper clip icon above the Reply button.
-
spinner. 7580 said:
Attaching sample DBI've taken a look. I've added a dashboard (Data Entry), since, for me, it makes the most sense to have a data entry form that is separate from Routes. Maybe I'm a bit old fashion, but having the Route Match tab on each record in the Routes table feels weird.
You can see that I've added a dynamic choice field based on the Miles input. Then you select a choice, the click the button and a record is created in the Ride Log table.
You can also stop using the Route_ID field for anything. In Ninox you make the link with reference fields.
In your case it is the Route Log Link field in the Routes table.
The other way to enter a new record in Ride Log, is to click on a record in the View element on the Data Entry dashboard and you will the Ride Log Link table. You can then click on Create Record and Ninox will link the Ride Log record with the current Route record. You don't need the Route_ID to keep the relationship.
-
spinner. 7580 said:
I can't find the 'this' function documented in the manual, what exactly does it do?Weird. 'this' tells Ninox to take the current record you are on and put it in a variable.
spinner. 7580 said:
Do I have the (N:1) relation backwards from Ride Log to Routes?I don't think so. 1 Route can have many Ride Log records related to it, since you can ride the same route many times.
I noticed you used both null and 0 to 'reset' the fields, is that a style issue or.... null for both works too...
It all depends on what kind of field you are trying to clear.
Miles := null; Routes := 0;Miles is a number field. If I put a 0 in it then it will have a value of 0. I want it to be empty so I have to use null.
Routes is a dynamic choice field, so to clear it out you make it equal to 0 not null.
The use of number() or text() with dynamic choice options seems a bit random, I typically have written the code the way I think it should work and if it doesn't I start using text() or number() until I get the right behavior.
It all depends on what you need from the dynamic choice field. If you need the record Id then you would use number(). If you want the text then text(). For example the record() field uses record Id, so you would use number(dynamic choice field name).
If I perform number() on the dynamic value name number( Route_ID + " - " + 'Base Miles' ) results in Route_ID
This I don't understand. The moment you add a + sign and have text following (e.g. "-"), you turn the formula field into a text field. That would mean putting number() in front should give you 0 since there is no number to get from a text string.
Since Route_ID and Base Miles are number fields why do you want to turn them into numbers again?
-
Fred
Fred said:
This I don't understand. The moment you add a + sign and have text following (e.g. "-"), you turn the formula field into a text field. That would mean putting number() in front should give you 0 since there is no number to get from a text string.So, if the Route dynamic choice displays values like this:
And then perform number(Route-X) it returns 1028
If the Route dynamic choice displays values like this:
And then perform number(Route-X) it still returns 1028
As you mentioned both 'Base Miles' and Route_ID are number fields.
I wanted to pull out Route_ID to display the full route description in a formula field based on the choice in Route_X, that way I can confirm that I picked the correct route since many of the routes may be subtlely different.
The Dynamic value name in Dynamic choice filed has this code:
Route_ID + " - " + 'Base Miles' ( in the first case above )
and
'Base Miles' + " - " + Route_ID ( in the second case above )
Regarding the this command.
Fred said:
Weird. 'this' tells Ninox to take the current record you are on and put it in a variable.In the case of your Dashboard, my assumption is that there isn't a record that it is sitting on as there is no associated table? Does it sort of "defer" looking at a record until the code references one?
Actually, looking at your code again, maybe this doesn't matter since tt isn't referred to after it is declared in line 1
whereas in my original DB post tt is referred to in line 11 and in that DB I'm sitting on the Routes Table not the "table-less" Dashboard in your example.
....well then again... maybe the "link" to a record for this happens because of the table reference in the dynamic choice button definition...
...I think I'm out in the weeds again -- thanks for playing along!!
-
-
spinner. 7580 said:
I wanted to pull out Route_ID to display the full route description in a formula field based on the choice in Route_X, that way I can confirm that I picked the correct route since many of the routes may be subtlely different.My mistake. I thought you were talking about a formula field not the dynamic choice display values.
So when you do number(dynamic choice) it is returning the record Id. I'm guessing that the Route_ID just happens to match the record Id for this one record. If you want to access data from the record then you can use the code from the variable from line 2 of the button:
let rtID := record(Routes,number(Routes));Then you can reference it like:
rtID.Route_IDAnd it will show the data from that record.
Regarding the this command.
In the case of your Dashboard, my assumption is that there isn't a record that it is sitting on as there is no associated table?
Actually, looking at your code again, maybe this doesn't matter since tt isn't referred to after it is declared in line 1
whereas in my original DB post tt is referred to in line 11 and in that DB I'm sitting on the Routes Table not the "table-less" Dashboard in your example
You are partially correct. There is always a table and at least 1 record in a dashboard (if there are more then some clean up is needed), so if you need to use data from fields in the dashboard then it can come in handy.
But yes for the sake of the button the 'this' function doesn't help.
You are also correct about why it is left over from. Just me not cleaning up my code.
You are a quick learner and seem to be getting a good grasp of how things work.
Content aside
- 1 yr agoLast active
- 18Replies
- 1650Views
-
2
Following