Populate portal with related records, then alter fields in portal without editing original record.

[Joe_Schmo]

Is there a way to populate a portal with related records and then be able to change values in the fields of the portal records without changing the original record they came from?

This would be easiest to explain as an inventory/invoice database. Say I have an "Invoice" table that contains a portal which displays records from an "Inventory" table. In my case the items in the portal are populated automatically based on a field outside the portal. This would be similar to a case were Customer A always places the same order, and choosing Customer A in the Invoice:Customer field would get all related records and fill out the items in the portal.

I would like to be able update certain fields in the portal after it has been populated without altering the original record that the field values in the portal came from- like altering an item description on a particular invoice, without having that change show up on all previous and future invoices that contain that item. I can make the records in the portal editable, but then every invoice with that inventory item in it reflects those changes.

I think it could be done by having a copy of the "Invoice" layout that doesn't use a portal and have the values from the first layout/portal copied to the second layout into new records. Would this be the right approach or is there a better way? Perhaps something that "breaks" the related records link the portal has to the "Inventory" table so that once it's populated it can be edited without affecting the original.

[comment]

See if this answers your question:

http://fmforums.com/forum/showpost.php?post/309136/

Note the handling of product's price.

[Joe_Schmo]

Hey, sorry- I didn't mean to hijack that other thread you linked to. Anyway, yes your example file in the other thread seems to do what I'm looking for, for the most part, with one exception-

The "customer" records in my database always order the same "products" so I want the "Line Items" portal on the "Invoice" layout to automatically populate with the related records of products for that customer. I know that this will require another table relating customers to products, and in my database I already have that. I also have the portal already set up to populate the products based on the customer, but they aren't lookup values, so editing a product actually changes the original record.

If I can get the portal to fill out all the related records of products based on choosing the customer on the Invoice, AND be able to edit those records in the portal without affecting the original Product record then it would be perfect. That way, if customer 1 always orders products Alpha, Bravo and Charlie, but on occasion my want to add or subtract to that order, then the user filling out the invoice will have the convenience of the Line Items portal autopopulating, yet still has the ability to alter the products being order.

[comment]

your example file in the other thread seems to do what I'm looking for, for the most part, with one exception

It's an addition, not an exception: a script that would create a record in LineItems for every one of the products related to the selected customer.

Hint: if you look at the chain of relationships:

Products 2 -< CustomerProducts >- Customers -< Invoices -< LineItems >- Products

you will see that as soon as you select a customer for an invoice, you can get a list of that customer's products as =

List ( CustomerProducts::ProductID )

Now you just need to loop through the list and create a new line item for each item of the list.

[Joe_Schmo]

I get what you're saying conceptually but I don't know how to go about it. I know all the relationships are there but I don't know how to generate the list. I see there is a "List" Function in the calculations. So does this mean that the list only exists in the calculation and not in a table somewhere? What would the script look like after the list function?

[comment]

Roughly (starting from Invoices layout):

Set Variable [ $invoiceID ; Invoices::InvoiceID ]

Set Variable [ $productIDs ; List ( CustomerProducts::ProductID ) ]

#

Go to Layout [ LineItems ]

Loop

Set Variable [ $i ; $i +1 ]

Exit Loop If [ $i > ValueCount ( $productIDs ) ]

New Record

Set Field [ LineItems::InvoiceID ; $invoiceID ]

Set Field [ LineItems::ProductID ; GetValue ( $productIDs ; $i ) ]

End Loop

#

Go to Layout [ original layout ]

[Joe_Schmo]

Roughly (starting from Invoices layout):

Roughly huh? More like PERFECT! I created a script in your invoice demo file first to test it and it worked like a charm! Now to apply it to my database. Thanks!

Couple of questions though-

Set Variable [ $i ; $i + 1 ] How does this work? It's like you are using the variable in the definition. The variable is named "$i" and you set it's value to "$i + 1". How does FM know what "i" is in the first place?

What is actually stored in the $i variable? The script exits the loop when $i > ValueCount $productID so I'm guessing it counts the number of progressions through the loop somehow but I just want to understand how.

Thanks again Comment!

[comment]

Any $variable that has not been assigned a value is empty, and empty + 1 equals 1.

[Joe_Schmo]

I've got the script filling out the Line Items but the portal showing those records in the Invoice isn't populating for some reason. Do you think you could take a look at it for me?

It will make more sense if you relate the names in my database to the invoice one we've been using as an example

LAYOUTS

Invoice = Safety Clearance

Line Items = SC_IsoPnts

TABLES/FIELDS

Line Items = Isolation Points

ProductID = Isolation Points:Component

CustomerID = Equipment Name

Choosing a piece of Equipment from the dropdown on the Safety Clearance layout DOES fill out the SC_IsoPnts records using your script

(like choosing a customer filled out the line items in the invoice).

But those new records in the SC_IsoPnts table aren't showing up in the Safety Clearance layout in the SC_IsoPnts portal

(aka the line items table is having new records created via the script, but then those records aren't showing up in the line items portal of the invoice).

I think it has something to do with my DB having more T/Os for each table than yours so it's just a matter of creating the right relationships and references to the fields of the right T/O but I've been trying to figure it out all day and can't get it.

By the way, when you start a new record in the Safety Clearance layout, just click out of the first field or choose the user "Petty" to get past the validation error.

SCUD.v0.2 copy.fp7.zip

[Joe_Schmo]

Here's the file you posted, edited using the script and suggestions you provided. It's working exactly as I would like to get mine working now. I just can't figure out why mine won't fill out the portal.

InvoicesDemo 2.fp7.zip

[comment]

I did take a look at it - but I am having an awfully hard time understanding your relationships, and how your tables translate to mine.

Why don't you use ID fields for your core relationships - so that it would be immediately clear which table is the parent and which the child?

[Joe_Schmo]

I did take a look at it - but I am having an awfully hard time understanding your relationships, and how your tables translate to mine.

Why don't you use ID fields for your core relationships - so that it would be immediately clear which table is the parent and which the child?

I know I should have used ID fields but it didn't occur to me until I had several tables and relationships built. And I totally understand your confusion- I had the same problem translating your script and suggestions in the first place. It is very similar in function, but it's confusing to figure out what translate to what.

[comment]

An ERD can help - even a crude one like:

Products 2 -< CustomerProducts >- Customers -< Invoices -< LineItems >- Products

[Joe_Schmo]

Isolation Points -<Equip_IsoPnts >- Equipment

• (Each piece of equipment has several Isolation Points used to isolate it)
  

Equip_IsoPnts -<Safety Clearance

• (A Safety Clearance is written for one Piece of Equipment, requiring several Isolation Points. The points should autofill in the portal)
  

Safety Clearance >-SC_IsoPnts >-Equip_IsoPnts

• The SC_IsoPnts portal on the Safety Clearance is like a Line Item portal on an Invoice. Is should populate with the Isolation Points for the chosen Equipment (like products for a customer)
  

Thanks again for trying to help. I know it's a mess right now. I'm back tracking on some relations that I already had built by doing it this way, so some stuff in there is irrelevant.

[comment]

Are you sure about this? It doesn't seem to make sense - alone or with the rest of it.

[Joe_Schmo]

Well, I'm sure I know how I want it to work. I'm not sure I'm describing it correctly though if you are questioning it. Did you check out the version of the invoice file that I reposted? I have it working exactly how I want this one to now.

It might help if I describe it from a functional user-end standpoint.

• User selects "Equipment" from dropdown on Safety Clearance layout
  
• Portal on this layout is populated with the names and related fields of Isolation Points
  
  • this info comes from the Isolation Points table, but only the points related to the chosen equipment show up (as defined in Equip_IsoPnts table).
    
  • The portal is populated via a script (the one you gave me for invoices) which takes the Safety Clearance # and Equipment name then creates $IsoPnts list and puts all the related points in new records in the SC_IsoPnts layout, which then shows up on the Safety Clearance layout as a portal
    

  [*]Once the portal is populated with the related points, the portal records can be added, removed or edited without altering the original set of Isolation Points that go with the chosen Equipment since they were lookup values

I'll try and draw something up as well and post it in a few.

Thanks.

[Joe_Schmo]

[comment]

Sorry, I am only getting more confused. The thing that bothers me is this:

A Safety Clearance belongs to one SC_IsoPnt.

A SC_IsoPnt belongs to one Equip_IsoPnt.

But also:

A Safety Clearance belongs to one Equip_IsoPnt.

Now, I don't know what these represent in real life, but it doesn't make sense to relate a grandchild directly to the grandparent.

[Joe_Schmo]

Sorry, I am only getting more confused. The thing that bothers me is this:

A Safety Clearance belongs to one SC_IsoPnt.

A SC_IsoPnt belongs to one Equip_IsoPnt.

But also:

A Safety Clearance belongs to one Equip_IsoPnt.

Now, I don't know what these represent in real life, but it doesn't make sense to relate a grandchild directly to the grandparent.

No, not quite-

There will be several records in the SC_IsoPnts table for a given Safety Clearance record (which has a unique Safety Clearance #). So many SC_IsoPnts belong to one Safety Clearance.

Isolation Points to Equip_IsoPnts are a many to many relation. Every piece of equipment will require several breakers and/or valves to be open/shut before work can begin on it. The SC_IsoPnts just copies the Isolation Points related to the current Equipment (taken from the Safety Clearance, and related via the Equip_IsoPnts table) in order to have a record of what points were used on that Safety Clearance, and still make them editable without affecting the originals.

A given Isolation Point (valve, breaker, etc...) can be used to isolate flow or power to more than one piece of equipment.

A given piece of equipment may need to be isolated for maintenance on several occasions. So a given Safety Clearance does belong to one Equipment, but not one Equip_IsoPnt. Each time an Equipment is tagged out for maintenance, different Isolation Points may be used.

I know it's confusing. Even if you don't understand what it represents in real life you've still been a big help.

Maybe if you just ignore the "Why" and figure out what's different between my database and the invoice one I reposted that would help. The invoice has the same grandchild to grandparent relationship that's throwing you off, but it works there. Just think of it like having a database that autofills the products a customer regularly orders (grandchildren) on an invoice (grandparent) by getting that relationship from a table that joins customers to products (child). This way the products (isolation points) autofill on the invoice (safety clearance) but if the customer wants to add or remove a product or change a quantity this time around you can do so without altering the standard order. You would be altering the Stnd_Order table I created in the invoice DB (like the SC_IsoPnt table) which keeps a record of the products on that invoice, instead of altering the Stnd_Order permanently (or the Equip_IsoPnts).

[comment]

I am afraid I stopped reading around here:

Isolation Points to Equip_IsoPnts are a many to many relation.

because that's not what your described relationships are. Anyway, I don't think there's much chance I'll understand this without exceeding the reasonable limits of a forum exchange. IIUC, you now have a working "prototype", so there's no reason why you shouldn't be able to figure it out.

[Joe_Schmo]

And I'm not sure what you're referring to by

because that's not what your described relationships are

I know this is hard to understand when virtually every table and field are based on a system you are unfamiliar with. That's why I was asking everything in the context of the invoice, at least up until I had it working in the invoice and couldn't get it working in my database. So, yes, I do have a working prototype but the same technique isn't working in my DB for some reason.

I appreciate you trying so hard to figure it out though. I know it's like a foreign language to you, the same way FileMaker was to me a month ago. Anyway, thanks for trying. I'll get it eventually. By the way, did you ever look at the invoice I reposted? It's not the same version you had posted in the previous thread you linked to. I modified it using the script you wrote and added a table. It would be easier to understand the type of relationships and functionality I'm after by seeing it work in the invoice first if you are still interested. Then it should make more sense in the Safety Clearance context.

[Joe_Schmo]

I think I got it. I had something to do with:

http://filemakertoday.com/com/showthread.php/2875-this-field-cannot-be-modified-until-a-valid-value

and one of my fields in the SC_IsoPnts table, which is the portal on the Safety Clearance layout, not having a value in it for the relationship to work. Still have to find a way to work around it, but the portal is working now.