0

Need to link each expense from this week to a record and then link it to the main table

Hello!
I have in this issue, three tables:
1st Table: Management (for each week there is a record.
2nd Table: Fixe Ausgaben (every record is one expense, which is recurring monthly on a specific date.)
3rd Table:  Zwischentabelle für fixe Ausgaben (it links to the 2nd Table (Fixe Ausgaben)  and the first Table (Management)

I want to have a loop which makes all this job for me.
So the loop is already doing all fine, except one thing:

It doesnt link each record from the array to his record in the table Nr. 3 

The loop does fine:
Creates from the number of records of the array new records in the 3rd Table and links them to the record in the 1st table.

I also attach my database here:
Test.ninox

7 replies

null
    • Fred
    • 3 wk ago
    • Reported - view

    You need to reference your for loop variable in the loop.

    for fixeAusgabenTabelle in ExpenseThisWeek do
    let thisExpense := this.ExpenseThisWeek;
    let newRec := (create 'Zwischentabelle für fixe Ausgaben');
    let thisExpense := this;
    newRec.(
        'Fixe Ausgaben' := fixeAusgabenTabelle;
        Management := t
    )
end

    I would recommend that you change the for loop variable fixeAusgabenTabelle as you use it earlier in your code for another set of data. Maybe something like:

    for loop1 in ExpenseThisWeek do
    let thisExpense := this.ExpenseThisWeek;
    let newRec := (create 'Zwischentabelle für fixe Ausgaben');
    let thisExpense := this;
    newRec.(
        'Fixe Ausgaben' := loop1;
        Management := t
    )
end

    so you can easily see which loop variable you are using. This comes in handy when you have loops inside of loops inside of loops. Yeah, don't ask.

    Here is what I get:

      • Michael_Blechinger.1
      • 3 wk ago
      • Reported - view

      oh woow, i was soo close to it. I didnt know that I loop1 doesnt need to be defined before, or whatever. I still dont understand what loop1 is still doing, or what it is in detail.
      Obviously it is the variable for one item of the array in one cycle of the loop. and in the next cycle, loop1 will be the next item in the array. 
      Am I right?

      • Fred
      • 3 wk ago
      • Reported - view

      I didnt know that I loop1 doesnt need to be defined before

      Yeah, that is where Ninox is different then regular programming languages. You don't need to predefine variables before using them.

      A bit of background on how to read a for loop statement.

      It starts with

      for [variable name] in [array or range] do
    code
end

      Everything in square brackets is replaced by the user.

      The variable name can be anything you want. You will see people use the letter "i' a lot. I like calling it loop1, but to each their own. Sometimes it is more descriptive if I'm doing loops inside of loops or have many loops. It stores the data of the instance of the array the loop is in. For example we can do something simple like:

      for loop1 in range(1,4) do
    loop1
end

      It says, loop through the numbers 1, 2, 3 and show the number. The first time through the loop, loop1 will equal 1. Second time it will equal 2. Third time 3.

      So if you have an array of record Ids then the loop variable will be equal to that record Id that it is on.

      • Michael_Blechinger.1
      • 3 wk ago
      • Reported - view

      thank you very much! Which this knowledge I would have saved hours of try and error.

      • Michael_Blechinger.1
      • 3 wk ago
      • Reported - view

       could you also look what is wrong there?
      I wanted to go now more in detail about query the records. It's special, because if the week is in two months, I need to get the records e.g. 29 to 30 and then 1 to 5.

      This way  I tried, doesnt work well. I was putting a if-else statement in the variable.

      let fixeAusgabenTabelle := (select 'Fixe Ausgaben');
let firstDay := 'Formel (erster Tag in der Woche)';
let lastDay := 'Formel(letzter Tag in der Woche)';
let lastMonthDay := 'Formel(letzter Tag im Monat der Woche)';
let ExpenseThisWeek := if lastDay > firstDay then
        fixeAusgabenTabelle[Abbuchungstag >= firstDay][Abbuchungstag <= lastDay].Nr
    else
        fixeAusgabenTabelle[Abbuchungstag >= 1][Abbuchungstag <= lastDay][Abbuchungstag >= firstDay][Abbuchungstag <= lastMonthDay].Nr
    end;
let t := this.Nr;
"/Loop for import all Expenses of this week/
";
for loop1 in ExpenseThisWeek do
    let thisExpense := this.ExpenseThisWeek;
    let newRec := (create 'Zwischentabelle für fixe Ausgaben');
    let thisExpense := this;
    newRec.(
        'Fixe Ausgaben' := loop1;
        Management := t
    )
end
      • Michael_Blechinger.1
      • 3 wk ago
      • Reported - view
      let fixeAusgabenTabelle := (select 'Fixe Ausgaben');
let firstDay := 'Formel (erster Tag in der Woche)';
let lastDay := 'Formel(letzter Tag in der Woche)';
let lastMonthDay := 'Formel(letzter Tag im Monat der Woche)';
let ExpenseOneMonthInWeek := fixeAusgabenTabelle[Abbuchungstag >= firstDay][Abbuchungstag <= lastDay]['Art der Wiederholung' = 1].Nr;
let ExpenseTwoMonthsOne := fixeAusgabenTabelle[Abbuchungstag >= firstDay][Abbuchungstag <= lastMonthDay]['Art der Wiederholung' = 1].Nr;
let ExpenseTwoMonthsTwo := fixeAusgabenTabelle[Abbuchungstag >= 1][Abbuchungstag <= lastDay]['Art der Wiederholung' = 1].Nr;
let t := this.Nr;
let ExpenseEveryWeek := fixeAusgabenTabelle['Art der Wiederholung' = 5].Nr;
"/Loop for import all Expenses of this week/
";
if lastDay < firstDay then
    for FirstMonth in ExpenseTwoMonthsOne do
        let thisExpense := this.ExpenseTwoMonthsOne;
        let newRec := (create 'Zwischentabelle für fixe Ausgaben');
        let thisExpense := this;
        newRec.(
            'Fixe Ausgaben' := FirstMonth;
            Management := t
        )
    end;
    for SecondMonth in ExpenseTwoMonthsTwo do
        let thisExpense := this.ExpenseTwoMonthsTwo;
        let newRec := (create 'Zwischentabelle für fixe Ausgaben');
        let thisExpense := this;
        newRec.(
            'Fixe Ausgaben' := SecondMonth;
            Management := t
        )
    end
else
    if lastDay > firstDay then
        for LoopOneMonthInWeek in ExpenseOneMonthInWeek do
            let thisExpense := this.ExpenseOneMonthInWeek;
            let newRec := (create 'Zwischentabelle für fixe Ausgaben');
            let thisExpense := this;
            newRec.(
                'Fixe Ausgaben' := LoopOneMonthInWeek;
                Management := t
            )
        end
    end
end;
for loopweekly in ExpenseEveryWeek do
    let thisExpense := this.ExpenseEveryWeek;
    let newRec := (create 'Zwischentabelle für fixe Ausgaben');
    let thisExpense := this;
    newRec.(
        'Fixe Ausgaben' := loopweekly;
        Management := t
    )
end

       I fixed it already!

      The code is working like that:
      if the weeks is in two months, I run two loops. One for the first months and the second loop for the second month. And if the week has just one month it runs a different loop.

      And then it runs after the if else statement one more loop because it also needs to get all expenses which are weekly. 

      The next step will be the import of the expenses which are just one time in year.

      • Fred
      • 3 wk ago
      • Reported - view

      You can try adding the array() command to combine the ExpenseTwoMonthsOne and ExpenseTwoMonthsTwo into 1 big array then you don't have to have two loops. It would look something like:

      let ExpenseTwoMonthsOne := fixeAusgabenTabelle[Abbuchungstag >= firstDay][Abbuchungstag <= lastMonthDay]['Art der Wiederholung' = 1].Nr;
let ExpenseTwoMonthsTwo := fixeAusgabenTabelle[Abbuchungstag >= 1][Abbuchungstag <= lastDay]['Art der Wiederholung' = 1].Nr;
let ExpenseTwoMonthsCombo := array(ExpenseTwoMonthsOne, ExpenseTwoMonthsTwo)
let t := this.Nr;

      Then the true part of your if statement can be modified to remove the 2nd loop and everything will be done in the first loop.

      if lastDay < firstDay then
    for ausgaben in ExpenseTwoMonthsCombo do
        let thisExpense := this.ExpenseTwoMonthsOne;
        let newRec := (create 'Zwischentabelle für fixe Ausgaben');
        let thisExpense := this;
        newRec.(
            'Fixe Ausgaben' := ausgaben;
            Management := t
        )
    end;
else

      I don't see where you use the variables in lines 3 and 5. Maybe you can get rid of them and make the code shorter.

Login to reply

Content aside

  • Status Answered
  • 3 wk agoLast active
  • 7Replies
  • 31Views
  • 2 Following