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

[Adrian_Enz]

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]

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]

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]

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]

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]

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]

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

[Adrian_Enz]

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]

You probably need to use virtual column for this purpose.

[Adrian_Enz]

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]

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]

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]

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]

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]

Excellent

[Aleksi_Alkio]

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]

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]

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]

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

[Aleksi_Alkio]

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?

[Adrian_Enz]

Yes you should be able to have more than one active milestone.

To the other two questions I don’t have an answer.

I know how to do it if I would have every milestone in one table but not in seperate tables.

I learned the most by myself about databases and programming. Maybe I’m making a logic mistake and this is not even possible?

I just have those requirements and this database structure is the only way I can think about, so that it doesn’t get out of hand with 300 columns in a table (if all milestones would be in one table).

[Aleksi_Alkio]

Don’t think how you would identify active milestones in the app. Think how you know them… do they have a status field or something? And do you want to show only one or all of them?