Copying Parent/Child Records

[barrymac20]

Hi All.

I have an app that feeds information to 3 Sharepoint lists. The lists are parent/child related.

List 1 (Quotes) contains information related all new quotes that our company receives.

List 2 (Products) contains a list of products that form part of a Quote

List 3 (Sections) contains a list of all sections that for part of a Product.

All lists are shown as galleries in the app are on separate screens.

When a Quote is highlighted I click a button and this opens up the Products screen and only shows products for the quote selected.

When a Product is highlighted I click a button and this opens up the Sections screen and only shows sections for the product selected.

The task at hand is to be able to create a copy of a record on any given screen/gallery but also create a copy of any children if there are any.

For example, if I create a copy of a Quote it will also create a copy of the related products and sections. If I create a copy of a product it will also create a copy of the related sections, etc.

I have looked at Shane Youngs tutorial on how to copy records and I have it working for each individual list but I'm looking for some ideas on how to also create copies of child/related records.

I hope that's clear enough and thanks in advance for any help.

Barry

[barrymac20]

Anyone got any ideas?

Currently trying to pull info from related tables into a collection, do some manipulation with the parent/child ID and send back to the list.

[WarrenBelz]

Hi @barrymac20 ,

"Three tier" structures are not the best idea when you are using a data source (SharePoint) that is not a Relational Database, so firstly, how big are these lists (and if time related, will the newest records do the job) and at what "level" (I assume Product) are you wanting to collect "line item" data?

[barrymac20]

@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.

[barrymac20]

1

[barrymac20]

2

[barrymac20]

@WarrenBelz  I just wrote and submitted a detailed reply and its now gone from here for some reason. I have asked in the Community Feedback forum if it can be added back in.

[WarrenBelz]

@barrymac20 ,

If you re-post it, I can respond, but I may not be online a lot today (Saturday morning here)

[barrymac20]

@WarrenBelz   My reply is back

[WarrenBelz]

@barrymac20 ,

I am not sure how to respond to that without creating a model to test it (I never use that structure). Have you resolved it?