1

Is it possible to link automatically (via a function) fields in two tables that contain the same text?

68 replies

null
    • Master and Commander of the Sea of Cortez
    • danberkey
    • 2 yrs ago
    • Reported - view

    I'm a bit mystified.  This code compiles with no errors but doesn't work:

    let ShipsLogRecordID := Id;
    let StartDate := 'Log Start Date';
    let EndDate := 'Log End Date';
    for DeckLogRec in (select DeckLog)["Log Date" >= StartDate and "Log Date" <= EndDate] do
        DeckLogRec.(ShipsLogRef := ShipsLogRecordID)
    end

    The code is on a record level button in a table called ShipsLog.  The ShipsLog record has fields to specify a date range.  For every record in the DeckLog that falls within the date range specified by the fields in the ShipsLog, the code should set the ShipsLogRef (a reference field in the DeckLog) to link to the ShipsLog.  It doesn't seem to do anything.  What am I doing wrong?

      • Sean
      • 2 yrs ago
      • Reported - view

      Mariner of the Sea of Cortez Hi Dan, You used double quotes for the field -Log Date- which makes it a string. Try it with single quotes and it should work.

      • Master and Commander of the Sea of Cortez
      • danberkey
      • 2 yrs ago
      • Reported - view

      Sean I suspected it had to be something trivial to the eye but significant to the syntax.  It works, thanks so much!

    • Arwin_Dustdar
    • 1 yr ago
    • Reported - view

    greetings from cologne (germany),

    Sean may I ask since the initial post is around 3 years old, if the database structure may have been changed since that?

    It is, that when I try to use your code example which begins with

    let myTable1Id := (select Table1).Id;
     

    I directly get an errormessage showing the field "Id" is unknown. I was expecting this might be a systems field, like "Nr", is it?

    thx

      • Sean
      • 1 yr ago
      • Reported - view

      Arwin Dustdar Hi, yes, you need to use Nr instead of Id.

    • strawberry_ocean
    • 1 yr ago
    • Reported - view

    I am having a similar problem, I am trying to program a button on a record in my Transactions table to automatically select the appropriate record from my Tax Table (N:1) .  

    This is what I have but I am still learning, it doesn't do anything when I click the button. 

     

      • Fred
      • 1 yr ago
      • Reported - view

      st You don't have a field called Id, so line 4 must be referring to the Ninox record Id. You can't change the Ninox record Id, so line 4 won't do anything. Plus I would recommend not calling any field Id as that can confuse Ninox. Why they would allow it is beyond me.

      I'm not sure what you are trying to do.

      So you are in the Transactions table and in line 1 you get the record Id and put it in a variable 'transId'.

      Then you create a variable, 'transYear', and put in the value of year of the Date field.

      Then you start loop that loops through all records in Tax Table where the year field matches the value in the transYear. Then this is where you lose me.

      Now you want to change a field in related records in the Transactions link of Tax Table to match the record Id of the current record. Which would assume that the current record would be part of the related records.

      What are you trying to do?

      On a side note, please start a new post instead of opening up a really old post.

      • strawberry_ocean
      • 1 yr ago
      • Reported - view

      Fred Sorry about that, I replied here because I feel like I am trying to accomplish a very similar thing. Ill move this to a new post instead. 

      • Fred
      • 1 yr ago
      • Reported - view

      Are you trying to link the current transaction record to a record in Tax Table that has the same Year value? If so then the loop is not needed as there is only 1 record in Tax Table.

      let transId := Id;
let transYear := number(year(date));
let taxRec := first(select 'Tax Table' where Year = transYear);
let 'Tax Table' := taxRec

      In line 3, we have to add the first() command since Ninox always assume you have multiple records in a select, even if you only return one.

      • strawberry_ocean
      • 1 yr ago
      • Reported - view

      Fred Yes! This worked, except I had to remove let from line 4. I almost had this earlier but I never wrapped my select function with first(). 

      Thank you for the help!

      • Fred
      • 1 yr ago
      • Reported - view

      st oops, you are correct with Line 4. Glad it all worked out.

    • Jarno_Vande_Walle
    • 1 yr ago
    • Reported - view

    Hi there

    I used the code from Ben and changed "parent" in

    do j.(parent := item(parentId,i))

    into "child" and this has worked very well for some time. Here is the full code:

    let parentId := (select parent).Id;
let parentField := (select parent).field;
let parentCt := count(parentId);
for i in range(0, parentCt) do
for j in (select child)[field = item(parentField, i)] do
j.(child := item(parentId, i))
end
end

    However, now that the data is expanding (+/- 1000 rows in both parent and child table); it takes considerably longer to retrieve the data. Any idea as to what the problem might be? Would it be a solution to limit the range, so instead of "0", which is the oldest row, Ninox should only look in the last 50 odd rows, for example?

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    May the value of “field” in either table be changed? To say it otherwise, once a child is linked to a parent, can it become necessary to link it to another parent?

    • Jarno_Vande_Walle
    • 1 yr ago
    • Reported - view

    Actually Alain, the field value is a formula:

    creation date + person Id

    So, one person can have several rows in the subtable (child) , whereas in the parent table it has to match the results based on the the same unique field value (also identical formula). The results in child table are pulled in from an external source. Since the formula is /creation date + person ID/, the value does not change after it is created. Now it can take to up to one minute before all the results in the parent have been matched with the child, and it feels like this time is increasing as the number of rows increases.

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    So, if if the children that have been linked to a parent stay linked with the same parent forever, one only needs to take care of the orphans, which should take less time than processing the whole tables again and again.

    for c in select child where child = null do
    c.(
        let f := field;
        child := first(select parent where f = field)
    )
end
      • Jarno_Vande_Walle
      • 1 yr ago
      • Reported - view

      Alain Fontaine hi again, I tested the new formula and it works very well indeed; somewhat faster than the previous formula. Still; it takes Ninox about a minute or so to match all the data and I believe this will only increase as the database grows. Since all the new data (in both the parent and child table) are the newest; I’m wondering If we could limit the database so that Ninox should only match the 25 latest rows in the two tables. Any ideas If this is possible?

      • Alain_Fontaine
      • 1 yr ago
      • Reported - view

      Jarno Vande Walle Doing so would also imply some kind of selection, which is probably the operation that takes time. Are you working in a client/server context? Putting the script inside a “do as server … end” clause could help.

    • Jarno_Vande_Walle
    • 1 yr ago
    • Reported - view

    Thank you Alain; I'm using the cloud version. I've tried the "do as server" in front of the formula but it still takes around 60 seconds to match the results. 😕

Login to reply

Content aside

  • 1 Likes
  • 1 yr agoLast active
  • 68Replies
  • 15934Views
  • 7 Following