I’m now starting a new project where we chang...

(Adrian Enz) #1

I’m now starting a new project where we change the network infrastructure from our business customers. This happens in a rollout and is achieved through various milestones.

We have two different services. For each service there are 12 milestones that need to be filled out by different persons. The goal is to see on each customer, what milestone is active and which milestones are in which status (not started, active, closed).

To achieve this I thought about making a table for our customers called customerDB. And for every milestone I make a separate database which is referenced throught a UniqueID or customer name to the customerDB.

Making those milestones visible on a customer is not a problem for me, I’m also working with slices to show the correct service milestones dependent on the service that is chosen in the customerDB.

My problem is, that I can’t find a way to put data from milestone databases to the customerDB when data in the milestone changes.

If I have 5 milestones with different status: Milestone 1 (closed) Milestone 2 (active) Milestone 3 (active) Milestone 4 (not started) Milestone 5 (not startet)

Is it possible that I have a column in the customerDB which is called “active milestone” that always shows the lowest active milestone? (In this case, milestone 2)

A solution to this would help me with a lot of other apps, as they get more and more complex.

I hope this is somehow understandable.

Thanks in advance.

(Adrian Enz) #2

Ah yes I thought about that. I’m planning to give each milestone a status column where the enum fields are “started” and “closed”. Only if a user choses “started” he can fill out the fields of the milestone. And the “closed” status is only possible if all fields are filled out. Also when someone “closes” a milestone there is a Mail sent to the person that needs to fill out the next milestone.

I need the following views: - Customer detail view with all the related milestones as shown on the image above. (achieved this)

  • Table view of each customer with the lowest milestone that has the status “started”.

Think of:

Customer 1, Milestone 3 Customer 2, Milestone 2 Customer 3, none Customer 4, Milestone 6

This is what I’m not able to achieve.

(Aleksi Alkio) #3

It sounds it could be a good idea to create a related milestone table. One customer table and then one milestone table where the reference is the customer ID. If you show your milestones in ascending order, you would see the smallest one in the beginning of inline view. When you have two different services, you could group them in that inline view as well.

(Adrian Enz) #4

So I would have only one table for every milestone from every customer?

I also thought about this, my problem is, that each milestone has 20 to 50 columns that need to be filled out (we gather a lot of data). I thought that the other design would be more clear?

(Aleksi Alkio) #5

You can do that with the expression like this sample…

=ANY(SORT( SELECT([Related Milestone1 Service1][Milestone],[STATUS]=“Started”)+ SELECT([Related Milestone2 Service1][Milestone],[STATUS]=“Started”)+ … SELECT([Related Milestone12 Service2][Milestone],[STATUS]=“Started”), FALSE))

(Adrian Enz) #6

That’s awesome Aleksi, thank you so much. I’ll try this out now and I’ll give you a feedback. But it really looks like what I was looking for.

(Aleksi Alkio) #7

I tested it with another app and it should work correctly.

(Adrian Enz) #8

I tried it out now and it works in the test window, but when I save a milestone table it doesn’t trigger the appformula in the customerDB (I think). How can I get an appformula in another table triggered when I don’t save in this table?

(Aleksi Alkio) #9

You probably need to use virtual column for this purpose.

(Adrian Enz) #10

I’ve trouble doing this with a virtual column (I don’t see how it solves this problem).

But maybe it would be possible with a spreadsheet formula?

(Aleksi Alkio) #11

Spreadsheet formula is not needed. If you do that with the normal column, it will not change the status without opening the record. You need to do it with the virtual column.

(Adrian Enz) #12

I don’t know how to do it with a virutal column. You’re saying that app formulas in virutal columns are excecutet even without saving in this table? Or do I need to make more steps?

(Adrian Enz) #13

My bad, I didn’t put the virtual column in my slice.

Works perfect Aleksi, I have so much use for this function, it really helped me a lot.

(Adrian Enz) #14

Also do I need to list up all customers in a table view where there is the name of the customer plus the active milestone.

(Aleksi Alkio) #15


(Aleksi Alkio) #16

For your first question… if you want to keep it more simple, you could add three tables, for both services and for customers. Customer table contains all customers and the Service#1 table contains all milestones for all customers. Same with the Service#2 table.

If all milestones have totally different questions, then you probably should create one table for every milestones.

(Adrian Enz) #17

They all have totally different questions.

My problem really is that I want to get specific data from referenced tables to the parent table.

I also have this problem in another app, and I’m looking for a function to collect data from referenced tables to other tables. Or is this not possible yet?

(Adrian Enz) #18

I hope the image in the attachement explains what I’m trying to achieve.

A second question would be if I can make the referenced tables a little more decent (because per reference there is only one row). I’m thinking about taking away the View and New button and make the whole block a little less tall.

Is that possible?

(Aleksi Alkio) #19

Yes it’s possible but the formula will be little bit complex :slight_smile:

(Aleksi Alkio) #20

What if you have more than one related milestone? How do you identify which is the active milestone? Can you have more than one active milestones at the same time?