Field data created by specific Portal selection

[xxx202xxx]

Let me try to explain.

I have 3 Tables called - "ContactManagement";"AssetManagement";"ServiceManageMent". All tables are related to each other based on ID: (equipment) and Contact: (Full Clients' Name).

My issue is with the layout I call "Service_Form". On this form I have several fields for typical help desk stuff but 3 fields I want to populate via my example. They are: [color:red]ID; [color:red]Asset; [color:red]Serial;. I have inserted a portal that relates to the "Contact" field that lists all equipment assigned to the [color:red]"Contact" that is pulled from the "AssetManagement" table.

Ok. . . so this is what I am trying to do. . . In the portal data lets say a contact has 7 assets assigned to them, this is all the portal will show. I want to be able to click on one item in the first column within the portal (labled, ID, no matter if it is the first or the fourth row) to insert data from that one row into specific fields on the layout.

example of portal row headers = [color:blue]ID_[color:blue]Hardware_[color:blue]Contact_[color:blue]Asset_[color:blue]Serial. . . . Example of portal data = [color:green]ID1-ID7, [color:green]Hardware1-Hardware7, [color:green]Contact (remains the same for all rows), [color:green]Asset1-Asset7 and [color:green]Serial1-Serial7

I want to be able to click on any [color:green]ID (1-7) (within the data range pulled) and have that data fill in the fields that I have named; [color:blue]"ID"; [color:blue]"Asset" ; and [color:blue]"Serial" since the portal is related by "contact" I would not need the contact's info.

The data that inputs into these fields will be used for reporting purposes from the ServiceManagement Table.

I hope this is clear because I am burnt out trying to figure this out.

[bcooney]

"All tables are related to each other based on ID: (equipment) and Contact: (Full Clients' Name). "

Sorry, stopped reading right there. Don't use names to relate tables (is "equipment" an ID or the name of the equipment?). There should be a unique ID associated with each client, and that is how you relate their record to other records.

[Josh Ormond]

Set Variable ($ID; PortalTable::ID)

Set Variable ($Asset; PortalTable::Asset)

Set Variable ($Serial; PortalTable::Serial)

Set Field (LayoutTable::ID; $ID)

Set Field (LayoutTable::Asset; $Asset)

Set Field (LayoutTable::Serial; $Serial)

That is the basic script. You may need some other things depending on what you are doing. The "PortalTable" is the table occurrence that the portal is based on. "LayoutTable" if the table your current layout is based on. Change those names to match your tables.

I didn't read your relationship set the same way bcooney did, but definitely make sure you are not relating things based on non-unique fields like names or descriptions or other such data.

[xxx202xxx]

"equipment ID or ID (equipment)", That is just a table header. I use specific codes for each asset like "D-1,D-2,D-3" or "M-1,M-2,M-3". As far as the contact relationship I used the FM starter solution's relationship example by relating "first" and "last" names between "AssetManagement" and "ContactManagement". I can change that to give each client a unique descriptor.

Edited January 16, 2010 by Guest

[bcooney]

It is important to understand that key values (those used in relationships) should NOT be manually entered, coded, meaningful values.

Your D-1, D-2 codes for assets is very fragile. Put an autoentered serial number w/Prohibit mod in each table and use that field as your key field.

I'm not very familiar with FM starter solutions. I find them unnecessarily complex and poorly written.

[xxx202xxx]

I know nothing about these complicated scripts. Will this script Allow me to click on any asset within my portal to populate the layout fields where the portal resides? Or is another script needed to be added on to the script you provided?

[bcooney]

I've reread your post and still am confused about what you're trying to achieve.

It seems that you are creating a service ticket. I would assume that a service ticket is for a related client, and that each service line item contains data relevant to the item repaired.

We're struggling here with your interface idea. You've decided that you want all items belonging to a client in a portal, and that clicking an item in the portal "populates" a service record. But don't you need more than one item in a service record?

Have you looked at related value lists? If I'm at all on the right track, I'd create a Service Ticket record, and populate the ClientID (either from starting at client or a popup window with a select list). Once you have the client in the Service record, you can build a related value list that will allow the tech to enter Service line items choosing only from items that are related to that client.

[xxx202xxx]

Thank you for taking the time to work with me on this issue.

Yes, you are correct, this portion of my DB is for the purpose of generating a service ticket. However, these service tickets are not exclusive to clients and their equipment. These service tickets are for any and everything that surrounds IT in my environment.

Examples would be. . . .assigned or non-assigned assets, software adds, program instruction and more. This ticketing system is my record of my day to day.

When I create a new service record, I have general information that populates specific fields automatically regardless of the issue.

All of my tickets are different in terms of their issue. Some tickets are not client based at all. The ones that are client based may have a client that has equipment problems and some may have non-equipment problems. Those tickets that are non-equipment based will not display any equipment (IDs, Serial Numbers) in the service ticket even though the portal will show assigned client equipment.

If a client does have an issue with assigned equipment (workstation W1, W2 or W3) in their location, then the portal that shows all of their assigned equipment can be used for me to click on the equipment's ID so it will auto populate other fields (like ID, Serial Number) in the service ticket layout instead of me doing extra typing.

You mention related value lists, I am not sure if I know what or how to create such a list. I am extremely raw at FM. I have created pop-up lists to populate software fields but I am not sure if that is what you are referring to whe you say related value list.

[bcooney]

Glad you're interested in stepping back and rethinking your data model.

I've built help desk systems. In fact, I wouldn't be surprised to discover an FM help desk system that you could just buy. The one I built isn't mine to sell...

A service ticket describes one problem. Does it have any children? Because if it does, specifics will go in the Service Line Items table.

Techniques that you need to learn are:

1. how to populate a foreign key (popup menu/popup select list, scripted from a parent).

2. how to use Lookup entry options and auto-entered calcs. This is for the data that "automatically populates."

3. Understanding normalization. For example, if I know the Equipment ID, I don't need to populate a Serial No field in Service Ticket. It's already entered in the Equipment Record. Store it once and refer to it thru relationships.

4. Related value lists. Yes, a more sophisticated value list technique. A related value list filters your value list by "going thru" a relationship. So, on a service line item, EquipmentID popup menu could be filtered to only allow selection from the client that's specified in the Service Ticket.

Another design consideration is how you want to handle all the different types of Service Tickets. You could simply have tab panels for each type, with the respective fields to fill in. This results in "holes" in the data, in that a "software add" will not have data in the fields that aren't needed to record the software add event. Is that a big deal? How are you expected to report your activity will probably determine the most suitable data model.

So, analyze the different service ticket types and let us know what fields overlap and which are exclusive to that type. You can have child tables from the Service Ticket to records for each type.

[xxx202xxx]

Based on what I have explained, is there a solution (script/calculation) that will allow me to accomplish what I am trying to accomplish?

Do you know of any books that I can purchase to help me learn about script writing, writing calculations and all the extra stuff you mentioned in your previous response?

The service ticket section is basically done. I don't plan on making it overly complicated. The only children I guess would be the notes section that I have not finalized as of yet.

[bruceR]

re books: have you started by completing the tutorial that comes with your Filemaker installation?

[bcooney]

You really need to understand how to best model your data, or you will hit a wall reporting. The techniques I mention are necessary for anything beyond a simple system.

You've fallen victim to FM's approachable interface. Do yourself a huge favor and either get a book (Coffey's, Cologon's) or simply do the tutorial.

[xxx202xxx]

Thank you all once again for your help. It was killing me that I could not put to practice some of the things discussed due to the extended weekend.

Once I returned back to work I built a script based off of what you suggested and it worked. However it still needs some tweaking and I am afraid my FM skills are not up to par to figure out the tweaks needed.

This is what I used:

Set Variable [serviceForm_ID:;Value:(AssetManagement::ID:)]

Set Field [serviceManagement::ID:; $ServiceForm_ID:]

By clicking on the Item in my portal under the ID column (example - M-013), it populated the current layout's field labeled "ID" which is a great start. I duplicated that script for the two other fields that I wanted to do the same.

My tweak scenario is this. I want to be able to click on the ID (example M-013) and have the two other fields (asset and serial) populate their respective fields without having to click on the items in the portal that corespond to those items. I need to shortened the data entry steps here. 1 click - 3 fields populated.

[Josh Ormond]

You can just set the script to set 3 variables...then set 3 fields. Just like I have it about (except with your field names).

[xxx202xxx]

Great, I will give it a go tomorrow.

[xxx202xxx]

Thanks, this works exactly the way I want it to work.