Join (merge) two dataverse tables.

[knyklu]

Hello,

to increase the performance of the app I'm going to split the table into two:
1. T_current (with records from last 5 days - ca. 500 records)
2. T_archiv (records older than Today()-5).

From time to time I will need to sum up ie. the production order, so it will be necessary to gram data from both tables.
Unfortunately, I cannot use non delegable queries to collect data from T_archive.
Is there any way to join two identical tables in power apps?

[Amik]

@knyklu - as I think I understand it, you have two Dataverse tables ("T_current" and "T_archiv"). Both tables share similar columns. You want to append (not merge) the T_archiv rows underneath the T_current rows to create single table.

Is that correct?

[knyklu]

@Amik 

Sorry for late answer.

Yes. Both tables have the same structure, and I want to join records from them in single table (collection) to perform filtering.
For example. I need to check what was the total orders quantity for the product, and some of the records for this product are in T_archiv. The probem is, that I can have more than 2000 records for this product in T_archive. So creating collecion from T_archive with records filtered by product, and then merge it with simmilar collecion filtered from T_current is not good solution because of delegation issue.

[Amik]

@knyklu - I am still not clear on what you're trying to do.

Merging a table means you want to horizontally combine two different tables with a different column structure into one table (usually based on a common value).

Appending a table means you want to append two tables which share the same column structure and columns types stacked vertically above one another.

The subject heading in your post indicates you want to merge two dataverse tables, but the content of your post talks about splitting a table into two.

Are there actually two dataverse tables? Or is there one table you want split out with different filtered criteria, and then append them back together into one single table? 

Assuming it is in the fact the latter, you can use:

//last 5 days
ClearCollect(
    T_current,
    Filter(
        'Your Table',
        'Created On' <= Today() && 'Created On' > DateAdd(
            Today(),
            -7,
            TimeUnit.Days
        )
    )
);
//greater than the last 5 days
ClearCollect(
    T_archiv ,
    Filter(
        'Your Table',
        'Created On' <= DateAdd(
            Today(),
            -5,
            TimeUnit.Days
        )
    )
);
ClearCollect(
    CombinedCollection,
    T_current,
    T_archiv 
)

[knyklu]

@Amik 

Sorry for my English and logic.

1. I want to append two table.
2. Both of them have the same structure (all columns are the same).

3. My scenario and some history.
Before I have had single table (T_main) where I kept records from a tracker. When the count of records reach 1500+ records the performance of searching, filtering, sorting dramatically went down.
One of the most important thing in the app (tracker) is to work with records from last 1-3 days.
So I have decided to split the T_main into two (T_current and T_archiv) - I have created a flow, which each day moves records older than 5 day from T_current to T_archiv). And all works fine (more less)

The problem is now, when I want to sum up the project where the records with data of this projects are in both tables and the project take long time.
For example. Some records of the project are "deeper" than 2000 records, so I came across the delegation issue.

That's why I thought, If I can append to tables into one (on fly, as a temporary table) it could be best solution for me. It doesn't matter if i will achieve it in powerapps, by flow, dataflow or so. (Before the PowerPlatform ages I had used to do this king of apps in MS Access, and there You could do with tables whatever You want.)
To be honest, the best form me is to stay with solution with single table, but all controls for printing data in powerapps are extremely lagged if the have to print 50+records. For discussed project to print data I use Detail List from Creator Kit, and I definitely need to have straight access to table (not via collection).

[Amik]

@knyklu - you can use the same code if you're using two separate tables. The only difference is that the code below is looking at two different tables:

//last 5 days
ClearCollect(
    T_current,
    Filter(
        'Your Current Table',
        'Created On' <= Today() && 'Created On' > DateAdd(
            Today(),
            -7,
            TimeUnit.Days
        )
    )
);
//greater than the last 5 days
ClearCollect(
    T_archiv ,
    Filter(
        'Your Archive Table',
        'Created On' <= DateAdd(
            Today(),
            -5,
            TimeUnit.Days
        )
    )
);
ClearCollect(
    CombinedCollection,
    T_current,
    T_archiv 
)

[knyklu]

@Amik 

Sorry, but we are misunderstanding.
T_current - there are only records from last 5 days.
T_archiv - records older than last 5 days.
I dont need to filter this records by date.

What I need to is to take from both this tables records related to searched order number.

I can do it by creating two collections (like You suggest it) with filtered records by searched order number, but in this case can get delegation issue. (In T_archive I could have for single order number more than 2000 records, so ClearCollect will not return all records).

To avoid this (delegation issue) I need to find other solution. My idea was to append records from this two table in third table (can be any a temporary table) and then filter records by order number.
I hope its clear now.

[Amik]

@knyklu - even if you use delegable functions like Filter, Power Apps will never return more than 2000 records at a time per operation - that is the row limit.

Regarding Collections, technically this is less to do with delegation and more to do with how many rows you can store in memory per app session. However, there are workarounds to increase how many rows you can return:

https://www.matthewdevaney.com/create-power-apps-collections-over-2000-rows-with-these-4-tricks/