Auto edit related data

[sal88]

Hi all

I have a layout with a few portals in it. When I make a change in the primary record I want the necessary fields to be copied in to the related records (the portal data).

At the moment I have an OnRecordCommit triggered script that goes to each portal and their records and makes the changes.

It does seem a bit messy though, especially as it pops up when I'm running other scripts.

Is there a standard/elegant way of automatically transferring data from the primary record to its related records?

TIA

[comment]

Is there a standard/elegant way of automatically transferring data from the primary record to its related records?

Well, yes. The standard way is not to do it at all. The very idea of a relational database is to store data in one place only. The data that describes the parent record would be stored in the parent table only. The data that describes the children would be stored in the child table only. Thee should be no need to copy anything in either direction.

An exception to this rule is when child records lookup data from the parent at creation, so that subsequent changes in the parent do not affect the child. A prime example of this is invoice line items looking up the current price from the product catalog. In the (very rare) case of requiring the child records to update the looked-up values from the parent, you can perform a relookup.

 

[sal88]

Thanks comment.

Yes it did used to adhere to normalisation, unfortunately my solution has a lot of complex calculations and reports that require the repetition of data. It wouldn't surprise me if this wasn't actually necessary and could be corrected. However with my time constraints I could really do with the cheat method at the moment  (I do feel a pang of shame!)

[Rick Whitelaw]

I fail to understand how de normalizing data would help with calculations as long as the tables are properly related. The use of extra table occurrences can allow tables to have a variety of relationships too. The data still exists in only one place.

[sal88]

Hi Rick

I am in the process of unravelling this conundrum to find out why I have actually taken this approach. I am producing a normalised version of one aspect of my solution and seeing where I end up.

I can off the top of my head think of one reason and that is invoicing:

We have a 'labour' layout, in which you can also sell multiple items/products via a portal. The portal actually points to the same table via the relationship, but uses the 'Item_' fields. This table is called 'Log'/

Now all records are assigned to an invoice by giving both labour/item records an invoice ID in the 'Log_Invoice_IDF' field.

In order to produce the invoice as a report/pdf I can simply perform a search for all records with that invoice - this seemed to me the cleanest way of doing it.

I'll bet there's a better way of doing that though?

ALSO

In my Customer layout I have two tabs, one for labour and one for items. In order for me to be able to see both data types they should both have the customer ID? This would require a script to copy the customer ID from the labour record to the items record.

TIA

Edited July 12, 2015 by sal88

[sal88]

^*bump*

^{It's the second aspect that I would really appreciate some guidance on:}

In my Customer layout I have two tabs, one for labour and one for items. In order for me to be able to see both data types they should both have the customer ID? This would require a script to copy the customer ID from the labour record to the items record.

 

Cheers

[eos]

Since we don't know too much about of your solution, it's not easy to understand what you're asking for.

An invoicing solution is usually built along these lines: an invoice “belongs” to a customer, and has line items that relate to “products” (which in your case can be of type “labour” or “item”).

Assuming you have

Customer --< Invoice --< LineItem >-- Product (Labour or Item)

you could use two portals into Product, filtered by Product::type = "Item" and Product::type = "Labour", respectively.

[sal88]

Yes that is how I have the invoicing, I think actually that it is fine for that aspect.

However the sticking point is displaying the data in portals on the customer record.

Ignoring the details i've already given, I have 3 tables, Clients, Labour, Items.

Clients is related  to Labour via the Client_ID/Client_IDF fields.

Labour is related to Items via the Labour ID/Labour_IDF fields.

When I am selling labour/items to a Client I go to the Labour layout and enter hours worked, within this layout is also the portal for the Items table.

My problem is that if I go on the Client's record to see their billing history, I want to have a portal to both the Labour table and the Items table.

The Labour portal is easy, it just looks up records from the Labour table where the Cient_ID matches the Client's ID.

But for the Items table there is no Client_ID (without doing the script that I mention). How am I supposed to list this data?

Thanks

 

 

[eos]

From your description, each Item record is related to a Labour record, which in turn is related to a Customer record:

Customer --< Labour --< Item

so simply displaying a portal into Item on a Customer layout should give you all Item** records for that customer.

**Actually, shouldn't that be LineItems, i.e. every record is an instance of an Item that you actually sold for a specific price, in the context of a specific job (labour)? If not, where do you store the items you offer, and their (current) price, status etc.?

This data model strikes me as a bit odd … unless you decided to leave away some “details”, and your Labour table is actually Invoices, and Items is LineItems.

Edited July 15, 2015 by eos

[sal88]

Yes that's exactly right, you could call them LineItems.

The reason for this model is simply because items are really only ever sold in the context of the labour that was done - we need to have them all in the same place.

Regarding the LineItems portal in the Customer record though, my understanding was that all that would be displayed is the first LineItem record that is seen in the relationship, instead of listing them all - because the relationship is going through the labour table.

[comment]

Can an invoice have more than one labor item?

[sal88]

Can an invoice have more than one labor item?

Yes

[comment]

Ok, so what exactly is in your Items table? Does it catalog both labor items and the other type of items -  the type that can only be sold in the context of a labor item (could we call these "parts"?)?

[sal88]

On the whole it just catalogs items that are sold alongside labour (which resides in the labour table).

There are occasional instances when there are items sold without labour, when this happens though the same layout is used, the labour record is still created, but it is basically blank.

I'd say they're more products/items than parts:

E.g. the labour record will say "installed server and upgraded network", "10 hours", and the related Items might be "Dell Server", "Gigabit switch", "Internal RDX drive".

Edited July 16, 2015 by sal88

[comment]

I see. I would still have a "Labor" ( or rather "Labor Hour") entry in the catalog, so that it can lookup the price of an hour and multiply it by the quantity - same as any other line item.

In any case, i don't see why your structure needs to be much more complicated than this:

This assumes that you will be adding new "parts" line-items from a layout of LineItems, after selecting the parent labor line-item with which they will be associated.

Note that the relationship is based on matching two fields:

so that the "parts" line-item automatically inherits the parent labor line-item's Invoice ID.

As the result of this:

• A portal to LineItems, placed on a layout of Invoices, will show all line-items (labor or part) alike of the current invoice; you can filter it to show only one kind, based on the ParentLineID field being empty or not;
• A portal to Parts, placed on a layout of Invoices will show only the parts line-items of the current invoice;

Similarly:

• A portal to LineItems, placed on a layout of Customers, will show all line-items (labor or part) alike of the current customer; you can filter it to show only one kind, based on the ParentLineID field being empty or not;
• A portal to Parts, placed on a layout of Customers will show only the parts line-items of the current customer.

There should never be a need to modify any related record as the result of modifying its parent, except in the extremely unlikely case of re-assigning a labor item to another invoice after some parts line-items have already been added to it.

Note that we are assuming here that there is a real business need to associate each parts line-item with a parent labor line-item.  I wonder if that's really the case.

Edited July 16, 2015 by comment

[sal88]

Hi Comment

Thanks so much for this! I haven't had a chance to read through it closely yet, I should do shortly and will let you know how I get on. BTW the first image is no longer showing, don't know if that's a temporary site problem or whether it needs re-uploading?

Thanks

[MikeKD]

I don't see the first image either.... I'd assumed it was just me and my Mac.

But huge thanks Comment - it's brilliant to read your POV on others problems.

 

Best wishes,

Mike

[comment]

The most strange thing has happened: as I was editing another post I have made, the image of the RG I have posted here appeared in that post, too. Naturally, I have deleted it there - with the net effect being that it disappeared from here too.

I have re-uploaded the image here - I hope it won't be replicated to any of my other posts...

 

Edited July 16, 2015 by comment