@WarrenBelz , thanks for your reply.
I take your point regarding three tier structures and Sharepoint. I have been able to make it work by carrying the ID from list 1 to list 2 as a foreign key and then to list 3. Image attached.
While this might not be the best idea, as you say, it works very well when creating these items. That is until I'm now trying to add a "copy" function.
I want the user to be able to "copy" at each of the 3 levels. The complex one being a copy at the top level that copies the full tree so to speak.
A copy at each level, of that level only, is straight forward. I am capturing the information for the item, storing in a variable and then patching the copied information back to the list.
Patch(Quotes, Defaults(Quotes),
{
QuoteNumber: "Copy - " & varThisQuoteInfo.QuoteNumber,
QuoteRevision: varThisQuoteInfo.QuoteRevision,
LaborLanded: varThisQuoteInfo.LaborLanded,
MaterialLanded: varThisQuoteInfo.MaterialLanded,
CustomerName: varThisQuoteInfo.CustomerName,
ProjectLocation: varThisQuoteInfo.ProjectLocation,
ProjectName: varThisQuoteInfo.ProjectName,
CustomerContact: varThisQuoteInfo.CustomerContact,
CustomerEmail: varThisQuoteInfo.CustomerEmail,
Margin: varThisQuoteInfo.Margin,
RepMargin: varThisQuoteInfo.RepMargin,
BusbarFinish: varThisQuoteInfo.BusbarFinish,
CopperRate: varThisQuoteInfo.CopperRate,
SteelRate_16Gauge: varThisQuoteInfo.SteelRate_16Gauge,
SteelRate_14Gauge: varThisQuoteInfo.SteelRate_14Gauge,
SteelRate_11Gauge: varThisQuoteInfo.SteelRate_11Gauge,
GP03Rate_14Gauge: varThisQuoteInfo.GP03Rate_14Gauge,
LaborRate: varThisQuoteInfo.LaborRate,
CustomerPhone: varThisQuoteInfo.CustomerPhone,
Status: varThisQuoteInfo.Status,
Archived: varThisQuoteInfo.Archived,
QuoteSellingPrice: varThisQuoteInfo.QuoteSellingPrice
});
Copying two levels is more complex as I need to ensure that the information copied at the lower level receives the new ID for the item at the upper level before it is sent to the list. I've been able to manage this by collecting all columns from the lower level table except the the ID column, and then adding the new ID for the upper level item to the collection. This works, although I'm sure the coding could be improved.
Patch(Quotes, Defaults(Quotes),
{
QuoteNumber: "Copy - " & varThisQuoteInfo.QuoteNumber,
QuoteRevision: varThisQuoteInfo.QuoteRevision,
LaborLanded: varThisQuoteInfo.LaborLanded,
MaterialLanded: varThisQuoteInfo.MaterialLanded,
CustomerName: varThisQuoteInfo.CustomerName,
ProjectLocation: varThisQuoteInfo.ProjectLocation,
ProjectName: varThisQuoteInfo.ProjectName,
CustomerContact: varThisQuoteInfo.CustomerContact,
CustomerEmail: varThisQuoteInfo.CustomerEmail,
Margin: varThisQuoteInfo.Margin,
RepMargin: varThisQuoteInfo.RepMargin,
BusbarFinish: varThisQuoteInfo.BusbarFinish,
CopperRate: varThisQuoteInfo.CopperRate,
SteelRate_16Gauge: varThisQuoteInfo.SteelRate_16Gauge,
SteelRate_14Gauge: varThisQuoteInfo.SteelRate_14Gauge,
SteelRate_11Gauge: varThisQuoteInfo.SteelRate_11Gauge,
GP03Rate_14Gauge: varThisQuoteInfo.GP03Rate_14Gauge,
LaborRate: varThisQuoteInfo.LaborRate,
CustomerPhone: varThisQuoteInfo.CustomerPhone,
Status: varThisQuoteInfo.Status,
Archived: varThisQuoteInfo.Archived,
QuoteSellingPrice: varThisQuoteInfo.QuoteSellingPrice
});
Collect(Products, ClearCollect(collCopiedProducts, AddColumns(ShowColumns(Filter(Products, QuoteID=varThisQuoteInfo.QuoteID And Archived ="No"), "Title", "ProductType", "ProductStandard", "ProductName", "Quantity", "ProductMargin", "RepMargin", "CopperManufacture", "SteelManufacture", "GP03SheetManufacture", "BusbarFinish", "BreakerBrand", "BreakerStandard", "BusbarInsulation", "Enclosure", "ThermalImaging", "Seismic", "SystemVoltage", "MainBusCurrentRating", "MainBusFaultRating", "MainBusConfiguration", "ProductEntry", "InstrumentationAndCableCost", "Colour", "Archived", "OtherProductCost", "GA_Notes"),"QuoteID", Last(Quotes).QuoteID)));
My next step is to figure out how to pull in the third level items as instead of having to deal with only one new ID from the upper level I will also have to deal with multiple new IDs from the second level.
So I guess I need help figuring out this bit but also in tidying up what I already have working.
