Relatioships after the fact

[John_Pierson]

I have an service/workorder app we’ve been using for over a year now. As it stands, we can see work orders and service tickets that are historical to sites, but we would like to now make the addresses linked, due to the way we entered in some residential sites (listed them under one umbrella site, instead of entering them each as an individual site every time a work order comes in). How would I go about linking these addresses and making them available in one window? Currently if I go the the site, I can see all related workorders or service tickets to that particular site. But if I go to the problem site, it’ll list hundreds of related wo/st with all the addresses. These are property management companies with hundreds of addresses per. We didn’t think we’d need to reference them after but we’re finding that it’d help to know if we’d been there in the past.

[Bellave_Jayaram]

Are you wanting to show ALL addresses but indicate those addresses serviced by a property management company with some icon next to it (for example, work order added, work order completed etc.?)

You can customize your properties table view by using grouping and format rules.

[John_Pierson]

No, What I’m looking to do is link addresses to workorders. Currently service tickets are linked to their workorder, and both are linked to a site. What I want to do is make them linked to an address also, but do so historically.

[Koichi_Tsuji]

I would like to know the current data schema, the basic tables and its structures, i.e the name of columns with type and how the relationship is made between tables for the concrete advice.

[John_Pierson]

So I’ve been thinking about how to describe it more clearly and I think I know where my roadblock is.
My app has a sites table that is the main reference point. In sites is the site name, address, etc. I have seperate tables for workorder and service ticket. Currently, if I look at a site, it’ll list related service tickets, workorders, as well as other tables that are related to that specific site. My problems is that for a few of our larger property maintenance customers, instead of entering each property as a site, we just use the management name as the site and fill in the address of the property as we go. The address is listed in the service ticket and workorder. The thought behind this was that typically, we only visit a site once. we’re finding that it woud now be useful to be able to see the history of that particular address now in the app when we fill out a workorder. Currently, I can search tickets and find the address and go from there but it’d be better if we can just have that automatically in front of us.
Doing a little more research, Im thinking LOOKUP might be a way, but I want it to return a whole row from the service ticket and work order tables which share the same address.
I should add that we have over 1000 individual addresses that are unreferenced right now, and I thought about adding an additional table for addresses but that would not be realistic for the past jobs. There has to be some easy way to reference this, and I’m just missing it.

[Koichi_Tsuji]

Hi there,

I probably understood what you want to achieve. What about this ?

First, you create anther master table, called “management” where you list the name of management company which look after multiple sites. ID column and name of management column should be fairly enough and then put the name of management as Label.

Once you create the new table, then next step is to add the new data filed to your site table. Let s name IDs for management and data type as REF, and referring to newly created management master table.

This will create parent/child relationship between management and site master tables. One management has “many sites”, so ONE to Many relationship is now established.
We already have site and workorder, service tickets are related as one to many, i.e. parent child relationship.

So now in terms for the tree structure of the tables, the Management table should stand as grand parent, then site as parent, followed by work-order and service tickets as child.

Now on the parent (site) table, we have grand parent, i.e. management, meaning the child, both work-order and service ticket table can get the name of grand parent, i.e. management name by using dereference. From child, we create new virtual column, named as “Management” with expression something like [Name of Site].[Management Company Name], depending on how you name the column.
Then we set the data type for this VC as REF as well, then refer to grand parent, Management table.

When we create new work-order, then user select the site, probably with dropdown list. Once saved, the child table will automatically have connection to grand parent, i.e. Management table.

By setting VC as ref and refer to grand parent, now we should have auto-generated list type of column in management table.

you create new view, whatever type you prefer for this management table, and you will see the inline view for both work-order and service ticket.
It might be best to select TABLE type and group by SITE for this inline view. Your user will see the name of management ,and inline view will indicate the list of service ticket, work-order , importantly GROUPED by the site. Easy to find the target ticket and order.

I hope I m not away from what you want to do.

[John_Pierson]

That is close and I follow, but this would be the solution only going forward, correct? I’m trying to connect the addresses I already have in the table (close to 1000 entries) for past work orders and service tickets, and keep the same structure going forward.

[Koichi_Tsuji]

I can’t give you any clear answer and further advice unless I see your exact table structure etc.