Hi there, so this is a bit difficult to explain but I'll try my best so make it sound as simple as possible:
Data Source = SQL
Amount of Rows = more than 2000 rows
Amount of Tables: 10+ (but for this scenario only 2)
Good to know: since I need more than 2000 rows, and since my Contracts table contains quite some Foreign Keys, I created a View in SQL where I basically (Left) Join the tables together. For example, in the table below you see Country. But in my Contracts table I have a column called Fk_Country and that is linked to my Countries table which has a Column named Country_Name. With the View I merged them together. The problem is that I can't delete or write against my View.... so here is my problem:
So this app which I'm creating is based on Contracts. And every Contract could have Insurances within the Contract.
So my Contracts table could look like this:
| Contract ID | Name | Country | Created Date | Created By | Pk_Contract |
| Alpha | ContractA | Germany | 22/06/2021 | John Doe | 55 |
| Bravo | Mercedes | Mexico | 18/07/2020 | Michael Brook | 70 |
Now let's say I want to add an Insurance to Bravo, I then click on the second row and then I click on a button on my page, which brings me to another screen.
This screen contains a Form which shows me details of Contract = Bravo, and beneath it, it shows me a Data Table. The OnVisible page of that screen looks like this:
ClearCollect(MemoryInsurancesView; Filter(InsurancesView; Fk_Contract = 'DataTable_Contracts'.Selected.Pk_Contract))
InsurancesView = the View I created in SQL, which uses the table Insurances, but the table Insurances contain Fk_Status, but since I need the Status_Name from the Statuses table... I created the InsurancesView which merges the Status_Name into Insurances by using the InsurancesView
MemoryInsurancesView = my collection based on my View from SQL.
Now the problem is that if someone wants to delete an Insurance from a Contract, it is not possible, since you can't delete anything from a View. When I try this:
ClearCollect(Insurances; Filter(InsurancesView; Fk_Contract = 'DataTable_Contracts'.Selected.Pk_Contract))
Then it DOES work, but I miss my LookUp columns such as Status_Name. I tried to leave Fk_Status in it and do a LookUp on the column itself, but I can't get it to work. And the formula that did work did not show me the correct Status_Name, the keys did not match.
I also tried the following:
ClearCollect(
InsuranceMemory;
Filter(AddColumns(
Insurances;
"StatusName";
LookUp(Statusses; Fk_Status= Pk_Status ;Status_Name)
); Fk_Contract = DataTable_Contracts.Selected.Pk_Contract)
);;
But this does not show me any rows at all... so I'm a bit lost and nobody from my company can help me out. So I hope one of you guys could give me a push into the right direction? Thanks in advance! If you need any more info, please let me know.
Summary: think of a receipt: a person goes to a store and buys some socks, shirts and pants. The receipt is 1 row, which is called "ReceiptID = ABC". ABC has 3 products attached to it. So when I click on ABC from my Receipts table, I go to the Products table. On top I see in a form which Receipt I am viewing (Receipt ABC) and below in a DataTable which Products are attached to it. If I want to delete Socks from the receipt, I want to select it and delete it so that Receipt ABC has no more Socks attached to it.
Problem: I am using Views from SQL and I can't delete against it. When using the standard table straight from SQL, the columns with Fk_Status etc. show up instead of Status_Name which make it confusing for the End User of my app.
