Hello all!
I am in the process of building an App with a scoring system for pieces of equipment across different sites. The equipment is filtered by each site in a dropdown, which works really well until my workarounds get past the 4000 item mark. If I select a site with say 5800 items, the code that I built for it shows no errors, but the ants at the top of the screen don't stop moving. I found a way past this by making an IF statement that will take me to a data table on another screen if the site code matches the text box with that many rows. It loads the 5800 rows extremely fast, but for some reason when I put everything together in the OnChange, it just doesn't like it. The last straw it had with me I think is when I tried to implement Ranking ID's in all of this so all of the equipment can be saved for the user to pick the item later, before that, it worked, but was very slow.
The original OnChange code for the site selector does a few ClearCollects that:
1. Grabs sublocation names and other info from SQL view
2. Splits here by
a. If site code matches 5800 item site, Navigate to new table screen(it used to be on this OnChange until I injected Ranking_ID's.
b. If not, then continue to 3.
3. 3 ClearCollects that can handle any sites with up to 4000 records by doing Ascending and Descending Collect then subtracting both in the last CC.
4. ClearCollect data from step 3 and 1 so that I have matching sublocation names and ID's
5. ClearCollect data from 4 and add Ranking ID's, so users can review or add to saved data at another time.
My main goal here was to have everything work under one gallery or table, but I can't build it all on either one, I tried doing the same for the table as well but still gave me moving ants. Below is the OnChange Site Selection code that works pretty well without the other code posted after this one.
Clear(colSublocation12); Clear(colSublocation1); Clear(colSublocation2);Clear(colSublocation3);
ClearCollect(
colSublocationEFP,
ShowColumns(
Filter(
'efp.product_vw_sublocation',
place_id = 'Site Code Reference'.Text
),
"sublocation",
"asset_id",
"place_id",
"user_def9"
)
);
If(
'Site Code Reference'.Text = "ST0000309998", Navigate('Test Screen',Cover),
If(
'Site Code Reference'.Text = "ST0000309998", false,
Clear(colSublocation12); Clear(colSublocation1); Clear(colSublocation2);Clear(colSublocation3);Clear(colSublocationAll);
ClearCollect(
colSublocation1,
SortByColumns(
Filter(
product,
'Site Code Reference'.Text = place_id
),
"product_id",
Ascending
)
);
ClearCollect(
colSublocation2,
SortByColumns(
Filter(
product,
'Site Code Reference'.Text = place_id
),
"product_id",
Descending
)
);
ClearCollect(
colSublocation12,
colSublocation1,
Filter(
colSublocation2,
Not(product_id in colSublocation1.product_id)
)
);
ClearCollect(
colSublocationAll,
{user_def7: " ",Ranking_ID: " "},
AddColumns(
colSublocation12,
"Real Sub-Location",
LookUp(
colSublocationEFP,
sublocation in colSublocation12[@sublocation],
user_def9
)
)
);ClearCollect(
colSublocationAll2,
AddColumns(colSublocationAll, "RankingID2",
LookUp(
RenameColumns(
Filter(
CoreView,
Place_ID = 'Site Code Reference'.Text), "Product_ID","ProductID"),
ProductID = Value(colSublocationAll[@product_id]),
Ranking_ID)))));
Below is what used to be after the first If statement above:
Clear(colSublocation12); Clear(colSublocation1); Clear(colSublocation2);Clear(colSublocation3);Clear(colSublocationEFP);
ClearCollect(
colSublocationEFP,
ShowColumns(
Filter(
'efp.product_vw_sublocation',
place_id = Label1.Text
),
"sublocation",
"asset_id",
"place_id",
"user_def9"
)
);
If(
"ST0000309998" in Label1.Text,
ClearCollect(
colSublocation1,
Filter(
product_row_numeric_auto_increment,
Label1.Text = place_id,
product_id_int > 92742 && product_id_int < 100550
)
);
ClearCollect(
colSublocation2,
Filter(
product_row_numeric_auto_increment,
Label1.Text = place_id,
product_id_int > 100549 && product_id_int < 102550
)
);
ClearCollect(
colSublocation3,
Filter(
product_row_numeric_auto_increment,
Label1.Text = place_id,
product_id_int > 102549 && product_id_int < 104365
)
);
ClearCollect(
colSublocation12,
AddColumns(colSublocation1, "RankingID",
LookUp(
RenameColumns(
Filter(
CoreView,
Place_ID = Label1.Text), "Product_ID","ProductID"),
ProductID = Value(colSublocation1[@product_id]),
Ranking_ID)),
AddColumns(colSublocation2, "RankingID",
LookUp(
RenameColumns(
Filter(
CoreView,
Place_ID = Label1.Text), "Product_ID","ProductID"),
ProductID = Value(colSublocation2[@product_id]),
Ranking_ID)),
AddColumns(colSublocation3, "RankingID",
LookUp(
RenameColumns(
Filter(
CoreView,
Place_ID = Label1.Text), "Product_ID","ProductID"),
ProductID = Value(colSublocation3[@product_id]),
Ranking_ID))
);
ClearCollect(
colSublocationAlltest,
AddColumns(
colSublocation12,
"Real Sub-Location",
LookUp(
colSublocationEFP,
sublocation in colSublocation12[@sublocation],
user_def9
))));
Reset(TextInput2)
My apologies for the lengthy explanation, I hope this makes sense and would be very grateful if there was an easier way to approach this without having to use two tables. I would really like the user to be able to view all of their items in their appropriate site in which none of them exceed 6000. I can give more information if need be.
Thanks for reading!
