2. PHPRunner Tips and Tricks
  3. Guide 85 – Relate 2 tables in a very visual and quick way
This topic is locked

Guide 85 – Relate 2 tables in a very visual and quick way

 		6/25/2024 3:02:24 PM
PHPRunner Tips and Tricks
fhumanes author

img alt
I have also started this example to help Rubén.
He explained to me that he was designing a Treasury Management system and that he had to reconcile the payment forecasts with the actual payments, in order to forecast the balances of his checking accounts in the medium term.

He imagined a system that, by dragging records from one panel (forecast) to another (real payments), the forecast records would be cancelled.

If this explanation is not clear, I will summarize, it is to relate some records in a table with other records in another table, but if possible in a very visual way.

Objetive

In a very visual, simple and fast way , relate records from the forecast table to the records from the actual payments table.

In this case, it is for Treasury management, but it can be used for a multitude of situations, such as:

  • Delivery notes and invoices.
  • Invoices and bank payment movements.
  • Students and classroom.
  • Students and optional subjects.
  • Etc.

DEMO: https://fhumanes.com/reconcile

img alt

If you are interested in this article continue reading it by clicking here.
fhumanes author 6/25/2024

Technical Solution

In the end, a solution was found with a DashBoard page with 3 panels:

  • (1) Forecasts without reconciliation
  • (2) Actual payments without reconciliation
  • (3) Reconciled actual payments (with or without forecast)

The way it works is to select 1 or more Forecast records (1) and an Real Payment record (2) and press the “Data Concile” button. This is the general reconciliation.

There is also the possibility that the payment was not planned and in this case 1 or more table records (2) are selected and the “No Concile” button is pressed.

In both cases, the selected records go to the panel (3) and if we want to undo any of the previous operations, what we have to do is select 1 or several records from the panel (3) and click the “Rollback” button.

JavaScript functionality has been added to the panels:

  • Highlighting the record that the mouse is over it.
  • Select or deselect the record by clicking anywhere in the record.
  • Validates how many checks (selected records) there are in the panel in order to activate the action button.
  • Multiple prior validations in JavaScript, before starting actions.
  • Notifications through SW Alert.
  • To obtain the checks I had to enable the “Delete” option, but I have hidden the buttons and disabled the execution of this code

In the “Data Concile” action, selected records are checked in panels (1) and (2). The selected records are captured with the PHPRunner API and this button “triggers” the execution in the panel (2), which has a hidden button “Select Real” and the execution in the panel (1).

If you are interested in this article continue reading it by clicking here.
C
 		Chris Whitehead 6/26/2024

Thanks, that does look interesting.
fhumanes author 7/1/2024

Correction has been made in:

  • When an error occurs in synchronization of the order of execution of the buttons in both panels in the “Data Concile” button, the process is automatically resolved by running again, without user intervention


  • In the buttons of the “REAL” panel, the table prefix is ​​eliminated, so that these buttons can be inside or outside the table, in said panel.



New code:

// Put your code here.
var message = result["txt"] + " !!!";
ajax.setMessage(message);

var error = result["error"];

buttom_forecast = window.buttom_forecast;

if ( error != '') { // Process with error
console.log('Error: '+ error);
timeout = setTimeout(repetButtom, 1000); // Wait 1 seg and repet action

/*
Swal.fire({
icon: 'error',
title: error,
text: 'The process has been aborted !!!',
footer: ''
})
*/
} else {
//Refresh panels
var page = Runner.dashboard.getElementPage("p_real_panel");
page.reload({a:'reload'});
var page = Runner.dashboard.getElementPage("p_concile_panel");
page.reload({a:'reload'});
var page = Runner.dashboard.getElementPage("p_forecast_panel");
page.reload({a:'reload'});
}

function repetButtom() {
$(buttom_forecast).click(); // Disparar botón de Forecast (-repetición por error en sincronismo
console.log('Repet in Forecast: '+Date.now());
}
fhumanes author 7/3/2024

Extension 07/03/2024
Functionality has been expanded to include a filter form on DashBoard panels.

img alt

I have called this new version “reconcile2”.
The form is an ADD page that is created with a dummy table. I think it is an excellent alternative to filter pages of this type.

DEMO: https://fhumanes.com/reconcile2