0

Problems with condition statement in a while do structure

Hi there! Hope you can help me out with this:

So, I'm building a Many-to-Many relationship between two tables, one for journal entries ("Asientos por Registrar") and another for Vendor Invoices ("Gastos").

I have a Gastos --> Asientos Contables 1:N relationship as the most common scenario is to have one Invoice and more than one payment registered in the journal (Asientos...).

In several cases, one payment covers for two or more Vendor Invoices. Thus, the need to have a many-to-many relationship.

For that purpose I've created a link table called "Pagos Asociados" (Associated Payments) with only two fields that point to both tables "Asientos..." and "Gastos". I've even nested Pagos into Asientos as it makes sense to have the payment breakdown in the journal entry.

 The thing is that user interface wise is not very efficient. Here's why:

From the Gastos table, to relate a journal entry ("Asientos...") I just had to click "Add existing records" and I would see the entries pending to associate to an expense (or income), which makes it a lot easier.

Whereas now, I have these two table fields for a similar purpose:

 

 

What I've managed so far, is to relate this record the "old way", meaning through the existing relationship in Asientos... and I've set a trigger after update to create a new record in the "Pagos Asociados" table so that when entering the next invoice (and of course I have to keep in mind the fact that a specific invoice has a shared payment) I can also search for existing records and there should be a short list.

I'm not sure if this is the best course of action. But still I'm facing a second issue: what if there's a third or fourth expense associated to that one payment (journal entry). So, I tried to code a while do structure which basically would generate new records in the Pagos table as long as there is a balance in the journal entry.

From image 1, you can see that the entry is for a total 1500€ (of course if used in a formula, I take into consideration the minus symbol) and there's only one payment breakdown registered for the first of three invoices (each of 500€).

As said before, I tried while structure but obviously I'm doing something wrong as every time it should be running, well..I have to force exit.

I tried to use an if statement to look for balance and last record field (for example, description != null) so that it only runs the code if that condition is met, but nothing.

let x := this;

let g := Gastos;

let n := (create Pagos);

n.(Gastos := g);

n.('Asientos por Registrar' := x);

let i := Importe*-1 - sum(Pagos.Gastos.Total)

while i > 0 and last(Pagos.Descripcion != null) do

            let n := (create Pagos);

n.(Gastos := g);

n.('Asientos por Registrar' := x);

end

 

Any suggestions will be appreciated as I'm sinking in futility.

3 replies

null
    • Daniel_Berrow
    • 7 mths ago
    • Reported - view

    Rather than making that a while loop, try a for loop

    for i in select pagos do
 let n := (create Pagos);
 n.(Gastos := g);
 n.('Asientos por Registrar' := x);
end

    you could also wrap it in a "do as server" to speed it up a bit too

    do as server
 for i in select pagos do
  let n := (create Pagos);
  n.(Gastos := g);
  n.('Asientos por Registrar' := x);
 end
end
    • Marisol_Echaide
    • 7 mths ago
    • Reported - view

    Thanks Daniel!

    Still doesn't deliver what I need. I don't understand why, this for loop statement creates more than one record on every trigger. I guess it's still missing the condition to only create one record as long as there is a balance in Asientos por Registrar. Meaning that every payment (record in Pagos) is deducted from the balance in Asientos and when this balance is 0, the trigger for this loop should stop.

    But maybe I'm just complicating things too much and there's a simpler workaround.

    • Fred
    • 6 mths ago
    • Reported - view

    Let me see if I understand your setup:

    1) you started with a 1:N between Gastos and Asientos por Registrar.

    Then you say:

     said:
    I have a Gastos --> Asientos Contables 1:N relationship as the most common scenario is to have one Invoice and more than one payment registered in the journal (Asientos...).

    I don't see this table in your pictures, or is Asientos por Registrar the table name you gave to the quoted relationship?

    2) you created a new N:N between Gastos > Pagos Asociados > Asientos por Registrar (or Asientos Contables)

    So once you do that then I would think you need to remove the first relationship so you don't get confused over which one to use.

    Instead of working within tables, may I suggest creating a Dashboard to do your data entry. You can select a Gastos and Asientos Contables then have a button that creates the Pagos Asociados for you.

    If you can upload a test DB we can get a better understanding of your structure.

Login to reply

Content aside

  • 6 mths agoLast active
  • 3Replies
  • 68Views
  • 2 Following