0

Link tables during import CSV

Does anyone know if this is possible - or do I have to run a script after import ?

I know how to link two tables from button where they both have for example same type of field for a member number , but would be excellent if I could do it via the import to the child.

 

Rgds

Leo

19 replies

null
    • Fred
    • 2 wk ago
    • Reported - view

    I was not able to get Ninox to recognized a number in a CSV to be the record Id of a reference field. It looks like you will need to import the reference record Id into another field then use a button to create the links.

      • Leo_Woer
      • 2 wk ago
      • Reported - view

       Yes I was afraid that this was so - thanks anyway

      • Leo_Woer
      • 2 wk ago
      • Reported - view

       Hi Fred - hope you can help me here.

      I have the following link script :

      for j in select medlemmer do
          let myText := j.Medlemsnr;
          for i in (select Betalingsliste)[Medlemmsnr = myText] do
              i.(medlemmer := j)
          end
      end

      and it works perfectly, however in the "Betalingsliste" (payment list), there are old members who are not in the present database "medlemmer" (members), and when running the link thes get created in the member database - ho do I avoid this ? My idea was something ala :

      let Xarray := select members.Medlemsnr

      for j in select Xarray do
          let myText := j.Medlemsnr;
          for i in (select Betalingsliste)[Medlemmsnr = myText] do
              i.(medlemmer := j)
          end
      end

      But it seems that it will not recognise the Xarray

      Hope you can help me here

      rgds

      Leo

      • Fred
      • 2 wk ago
      • Reported - view

      Ninox lets you save the second code?

      In the tables members and Betalingsliste, what kind of field is Medlemsnr?

      • Leo_Woer
      • 2 wk ago
      • Reported - view

       both are numeric

      • Fred
      • 2 wk ago
      • Reported - view

       

      let Xarray := select members.Medlemsnr
for j in select Xarray do
    let myText := j.Medlemsnr;
    for i in (select Betalingsliste)[Medlemmsnr = myText] do
        i.(medlemmer := j)
    end
end

      Then line 3 won't work since the variable Xarray only contains numbers. It is not recordId so you have no where to go.

      You can just modify the code to:

      let Xarray := select members.Medlemsnr
for j in select Xarray do
    for i in (select Betalingsliste)[Medlemmsnr = j] do
        i.(medlemmer := j)
    end
end

      But I don't think it will make a difference with your problem of records in Betalingsliste with Medlemsnr that do not exist in medlemmer.

      So my question is what do you want to happen with records in Betalingsliste that have no match in medlemmer?

      • Leo_Woer
      • 2 wk ago
      • Reported - view

      I need them to stay in betalingsliste

      • Fred
      • 2 wk ago
      • Reported - view

      I'm confused. You are not deleting any records, you are only linking the medlemmer reference field.

      Is there other code you are not showing me that deletes records?

      As of now any Betalinsliste records that don't have a matching medlemmer record will not be linked. Which makes sense.

      • Leo_Woer
      • 12 days ago
      • Reported - view

       No there is no other code - and frankly I was also confused that the earlier script created records which truly not should be in the link array, but sometime IT has its own life. 

      Regards

      Leo

      • Leo_Woer
      • 7 days ago
      • Reported - view

        Do you know if you can set a link to field with value from a script  I have tried the usual link script - but it doesn't work - this script in my button :

      let myText := 'Foreningsnavn fra Economics';
      let Xok := "Linket";
      let Xforkortelse := Forkortelse;
      for j in select 'Foreninger fra Economics' where Navn = myText do
          for i in (select medlemmer)[Forkortelse = Xforkortelse] do
              i.(Forening := j);
              i.(Foreninggl := Xok)
          end
      end

      I have in my members the following fields :

      link to field called 'Foreninger fra Economics'

      Text field called 'Forkortelse

      text field called 'Foreningsnavn fra Economics'

      The whole idea is that when this operation is over we only have the link to field, which mus be done for all new members, and that we set this link to all the existing in the table called 'medlemmer' I have already via bulk edit et the Forkortelse on each member.

      any idea of what to do ?

       

      rgds

      Leo

      • Fred
      • 7 days ago
      • Reported - view

      can you provide a sample DB? What part is not working?

      • Leo_Woer
      • 7 days ago
      • Reported - view

       Hi Fred

      have looked much more into it - I think that the link gives no sense, as this is only link to with no composition.

      I did the following instead :

      created a text field "Forening" where I set the name of the chosen union (from 'Foreninger fra Economics') from update trigger into this field and then let the two fields replace each other (if a union is chosen then hide 'Foreninger fra Economics', if the field 'Forening' is set then display this field and vice versa. this off course only counts for new members, but for all the old  I ran the following script :

      let myText := 'Foreningsnavn fra Economics';
      let Xforkortelse := Forkortelse;
      for i in (select medlemmer)[Forkort = Xforkortelse] do
          i.(Forening := myText);
          i.(Foreningsredigering := false)
      end

      I can send you the DB if you still think that it could be done to set the mentioned link field - the reason for no composition is that foreninger can have many members and members can be part of many unions ('Foreninger')

      Thanks

      Leo

      • Fred
      • 7 days ago
      • Reported - view

      If this works for you then you don't need to upload a sample DB. 

      I can't say right now about setting the links you want until I can see the DB.

      • Leo_Woer
      • 7 days ago
      • Reported - view

       this works - thank you a lot for spending time again.

      Have a nice weekend.

      rgds

      Leo

    • Leo_Woer
    • 2 wk ago
    • Reported - view

    sorry Fred is occupied the 172 hour then I return

    • Leo_Woer
    • 2 wk ago
    • Reported - view

    Hi again Fred I am all yours now.

    I did your script (changed members yo medlemmer) - buy iy doesn't do anything.

    To further explain - I only want to link the records from Betalinggsliste for members who are present in medlemmer. The rest of the imported records should only be in the table Betalingsliste for information purpose - these records are members who once were members of this union but we need to store their payment history.

    Hope this gives sense,

    Maybe we could have an if statement in the original link script which sys something ala if the Betalingsliste,Medlemsnr does not exist in meters then skip the linkingscript and go to next record, but how to do that I don't know. I think that you in Ninox can say if exist then link else skip - so maybe this is a way.

    Any suggestions ? 

      • Fred
      • 10 days ago
      • Reported - view

      oops, try:

      let getAllMedlemmerRecs := select Medlemmer;
for i in (select Betalingsliste) do
    let matchingMedlemmerRec := first(getAllMedlemmerRecs[Medlemmsnr = i.Medlemmsnr]);
    if matchingMedlemmerRec > 0 then
        i.(medlemmer := matchingMedlemmerRec)
    end
end

      If you have a lot of records you can wrap the whole thing in a do as server.

      • Leo_Woer
      • 9 days ago
      • Reported - view

      Thanks a lot Fred

    • Leo_Woer
    • 2 wk ago
    • Reported - view

    SOLVED

    I changed the original script to following :

    for j in select medlemmer do
        let myText := j.Medlemsnr;
        for i in (select Betalingsliste where Medlemmsnr = myText) do
            i.(medlemmer := j)
        end
    end

    now I have all the existing records in medlemmer linked to the correct records in Betalingsliste, and all the non existing still resides in Betalingsliste.

    Thanks for you help until now - probably not the last time.

    Have a nice evening 

    Leo

Login to reply

Content aside

Categories

View all topics
  • 7 days agoLast active
  • 19Replies
  • 89Views
  • 2 Following