Array of ID to selection

Hi,
I decide to give you a code that was soooo important for me. I have time, confined at home :slight_smile:

CONTEXT
The goal is to get a selection in the same order as the ids contained in a longint array.
This is to be used for listbox, with an array for ids, which is always needed.
Why I need it sorted ? Just to be able to have a bijection between my listbox and my table, whenever I want/need it.
I’m sure you will understand !

4D can do that (selection ordered) from an array of record number doing this:
CREATE SELECTION FROM ARRAY
but that suppose to have an extra array in the listbox for the record numbers, which is stupid !

You can get a selection from an ids array doing this:
QUERY WITH ARRAY ( [myTable]ID ; array_ids )
but you get something in a totally random order, nothing based on array_ids.

So, you have a bijection from selection to array using
SELECTION TO ARRAY

But nothing for the bijection from array to selection :frowning:

SOLUTION
I wont explain how it works, just read carefully the code, this is not that easy but it works for me from more than 6 years in plenty of sites and databases.

The initial idea has been given to me by Roland Lannuzel, a BIG thanks to him!
Here is my code:

How to call:
<code 4D>
$ptr_table:=->[Banks] // My table
$ptr_T_LB_id:= OBJECT Get pointer(Object named;“lb_ids”) // my LB ids column array
ARRAY LONGINT($T_LB_id;0)
COPY ARRAY($ptr_T_LB_id->;$T_LB_id) // Protect LB, as the array will be sorted
y_selectionFromPrimaryKey ($ptr_table;->$T_LB_id)
</code 4D>

y_selectionFromPrimaryKey method:
<code 4D>
// ----------------------------------------------------
// Nom utilisateur (OS) : Olivier Grimbert
// Date et heure : 16/10/17, 17:19:00
// ----------------------------------------------------
// Méthode : y_selectionFromPrimaryKey
// Description
// from a ID array, load a selection ordered in the same
// Thanks to Rolland Lannuzel
// When I wrote this, only me and god knew. Now, only god does :slight_smile:
//
// Paramètres
// $1 : the table to work with. The first field is supposed to be the primary key !
// $2 : ptr to ids array ## WILL BE SORTED DUPLICATE IT FROM LB !!!
// ----------------------------------------------------

C_POINTER($table;$ptrT_ids)
$table:=$1
$ptrT_ids:=$2

C_BOOLEAN($isOk)
$isOk:=True

ARRAY LONGINT($t_recNum;0)
ARRAY LONGINT($t_ids;0)

QUERY WITH ARRAY(Field(Table($table);1)->;$ptrT_ids->)
SELECTION TO ARRAY($table->;$t_recNum;Field(Table($table);1)->;$t_ids)
SORT ARRAY($t_ids;$t_recNum;>) // To transform in IDs referentiel

// Remove inex
// use of “Find in sorted array” allowed as sorted by ids
C_LONGINT($i;$tt)
If (Size of array($t_ids)#Size of array($ptrT_ids->)) // some Ids not found, they must have been deleted.
$tt:=Size of array($ptrT_ids->)
For ($i;$tt;1;-1)
If (Not(Find in sorted array($t_ids;$ptrT_ids->{$i};>)))
DELETE FROM ARRAY($ptrT_ids->;$i) // value not found, remove it from _orderedIDs
End if
End for
End if

$tt:=Size of array($ptrT_ids->)
ARRAY LONGINT($t_originalOrder;$tt)
For ($i;1;$tt) // memorise the original ordering
$t_originalOrder{$i}:=$i //1,2,3,4,5,6,7…$n
End for
SORT ARRAY($ptrT_ids->;$t_originalOrder;>) // order original array, scramble sequential array
SORT ARRAY($t_originalOrder;$t_recNum;>) // back to original order WITH THE $_RecNum this time !!!
CREATE SELECTION FROM ARRAY($table->;$t_recNum) // and rebuild a selection according to this $_RecNum array

$0:=$isOk

</code 4D>