Filter one portal based on another filtered portal

[Peter Barfield]

Is it possible to filter a portal based on another filtered portal content where a relationship exists. 

My question stems from a scenario where I have a jobs table and a plant (being machinery) table. I am trying to figure out a way to filter a complete list of plant to only show those that are not allocated to a specific day on the job table. 

I guess I am just seeing if it is at all possible or is there a better method to do it.

[comment]

In general,  portal filtering works at layout level and you have no access to its results (other than viewing them on screen).  I am not sure how it would help in your scenario even if it were possible - but then I did not fully understand your description. 

[Peter Barfield]

Comment, 

Thanks for the reply.

I was afraid of that response yet, expecting it.

I will try and explain my situation. I have created a db for a family members business and on the whole it is doing what they were after. The db basically is a job allocation come invoicing solution. They operate plant.

My brother in law came to me this morning with a request for the allocator to have a screen which would replicate his day planner sort of.

What he wants is basically the screen divided into 3. The first "column" would be a space for creation of a new job, the 2nd would be a listing of the plant and their status for the day. I.e. Allocated or unallocated. And the third would be the same as the 2nd column save for the fact that would be broken down into 6 "boxes" (I'm thinking portals perhaps) these would show the previous days allocations the next day and the next so on until all boxes are full. In other words the first "portal" would be yesterday the 2nd tomorrow and so on.

i have a table for the plant that is related to the job table by a plant Id field. 

The purpose is that the allocator is not completely computer literate ( actually he is totally illiterate.) and he is pen and paper person (old school)  thus the effort to try to replicate and give him a good snap shot screen. 

I may very well be looking at this totally the wrong way and am happy for any suggestions on what might be a better way forward or a way to implement what he wants.

IHave used a TOC of the plant table with  is it a ?Cartesian join that shows all plant listed in the respective table. I guess I just need a way to filter that to show the allocation status for the given day.

hope this makes a little more sense. I have had a brain zapping day trying to make this work.

 

[comment]

 I am having trouble understanding this part:

3 minutes ago, Peter Barfield said:

i have a table for the plant that is related to the job table by a plant Id field. 

I would think that the relationship between Jobs and Plant is many-to-many and expect to see a join table between them. Unless PlantID is a checkbox in the Jobs table?

I am also confused by your using jobs and days interchangeably  - are they the same (i.e. no more than one job per day)?

[Peter Barfield]

Comment, I think you are right in that I have a table called plant with a primary ID and in the jobs table I have a key field for plant. With a relation to both of many to many.

now for the next part. I will try and explain. There can only be 1 job at a time for any plant. Albeit it could involve 2 jobs on the one day. They will however, be different records.Though that is rare as more often jobs take the best part of the day to complete.

So in the job table I have a field called Job Date which obviously indicates the day the job occurs.

somehow I need to be able to tell if the plant is being used for a job on a given day and then remove it from a list of available plant.

Does this throw any light on the subject or am I still clear as mud? 😀

[comment]

I am still unsure how exactly you assign more than one plant to a job.

 

[Peter Barfield]

Sorry, comment. Only 1 plant per job or a clearer understanding would be 1 job sheet per plant. Due to the way invoices have to be created for contractual reasons every plant going to a site even if there are 3 plant on site they will all have their own job sheet, driver, offsider etc. so therefore 1 plant to 1 Job sheet. I know that sounds strange however, different contractors on he one site may very well have different plant on site and therefore invoices go to different departments or sometimes totally different companies.

[comment]

See if the attached demo helps:

 

AvailablePlants.fmp12

[Peter Barfield]

Comment,

I can see what you have done here and that is a different angle to what I was thinking. Sometimes a fresh mind can help. I will play around with this tomorrow and I will duly report on my success (keeping it positive).It certainly on face value appears to do exactly what I want.

Thanks for the effort you have taken.

[Peter Barfield]

Comment, that works perfectly for the given day. I.e. Global Date. My question if you are able to help is applying the same concept to pick up unassigned from the global Date say +1 giving you the next day. In another portal. I think I tried to describe last night that they want for want of a better expression a week view broken up and displayed per day. My thought was 6 portals in "column" 3 each replicating each other except picking up the global +1, +2 etc.

thanks again for that work yesterday.

[comment]

Do they also want 6 portals showing the jobs for tomorrow, the day after tomorrow, etc.?  Or just the unassigned plants for each day?

Edited August 24, 2017 by comment

[Peter Barfield]

Just the unassigned plant for those days

Comment I received a notification from you that does not appear here for some reason

you said 

 

comment said: I would try converting the last portal to a filtered one, based on a relationship using the x relational operator. Then just add more portals and change the filtering expression. That would save you the need to define 6 global fields and 6 relationships. With only a few jobs per day, and a fixed amount of plants it should be fast enough - but you need to test this for real.  I have created a relationship of x from the unassigned plant to a TOC of that. Is that what you meant?

[comment]

I don't see how you can show unassigned plants for any day without having a list of assigned plants for that day first. So if you want to avoid having to duplicate the entire TOG 6 times, I would move to a scripted solution: populate 6 variables with a list of assigned PlantIDs for each day, then use these to filter 6 portals, all based on a single relationship showing all plants.  Run the script on entering the layout and on change of the global date value.

With only a few jobs and a fixed amount of plants, this shouldn't be too slow - but of course you need to test this for real in your environment.

[Peter Barfield]

Comment, you have completely got me there.

how would 1 go about gathering the list by scripting? Do you have to loop through the records from the plant unassigned table? Or am I thinking this through all wrong?. How do you filter a portal via script? I know I can filter a portal from the portal setup but I am not aware how to do that via script.

Sorry to ask so many questions once I get a grip on it I am sure i will be fine.

also in one sense the assigned jobs are there I just have to have a way of building the list by looking at the global Date + 1 day, 2 days etc.

as the assigned plants come from a relationship with the jobs table based on dates I thought I might be able to tap that information by virtue of the above mentioned criteria. 

Maybe I am clear as mud again in my explanation if I am I apologise.

[comment]

The script is only to populate the variables. You then use the variables to filter the portals from the portal setup.

A simple way to populate the variables is to increment the global date field by one each time (do this after Freeze Window) and setting a variable to =

List ( Jobs::PlantID )

Do this 6 times then reset the global field to what it was.

 

Here's a quick demo:

AvailablePlants2.fmp12

 

 

 

Edited August 24, 2017 by comment

[Peter Barfield]

That works a treat. Only 1 issue and I have played around with the script but I cannot get it to show the day previous days unallocated. If I follow the script I can see what it does so I created another variable similar to yours but calculated to go gdate - 1 however that falls flat and sets the field at the end to about 10 days prior to the current Gdate any ideas on where that might be failing? Other than that I am just about completely happy. I have used script triggers on field modify or exit depending to refresh and show everything correctly.

 

once again thanks for your input.

[comment]

26 minutes ago, Peter Barfield said:

I created another variable similar to yours but calculated to go gdate - 1 however that falls flat and sets the field at the end to about 10 days prior to the current Gdate

I am afraid I don't quite follow your description. Does it create the variable correctly and only fails to restore the original gDate value, or something else? Why don't you post the actual script steps instead of describing them?

[Peter Barfield]

Comment, I will do just that however, at the moment I am not able to access my computer and will be on the road for a 6 hour drive home. When I get home I will post it. 

Cheers

[Peter Barfield]

Comment,

I have attached a screenshot of the script in question.

As I said going forward in days via your script is a pearler and works without flaw. Just cannot seem to get it to go backwards in days to show the past.

As you can see I have imitated your script added the variable $j (I used J as it was the next after i which you used) and created a Global variable same as you did however just threw a 1 before the final letter to give it a differentname obviously and changed the Weekview Interface _g_selectedDate to -1 instead of +1 (my thinking is this would go backwards in dates not forwards.

The only thing I did not alter (or add in was the last  line (line 19) i moved your code to the end and so did not perform that after the first loop. ( i did however try with it replicated although that did not work either)

 

maybe I am not fully understanding the script although I think I do.

Cheers

 

[comment]

 I think you're making this more complicated that necessary. If you want to show 6 days back and 6 days forward, start with $i being -6  and increment it by 1 until it becomes 6. You can reuse the same variable name, with negative and positive repetition numbers. 

For simplicity, start with gDate being -6 too, and at the end set it back by $i. Or define another variable to remember its original value and use it to restore it at the end.

 

[Peter Barfield]

Got it. I didn't realise that the variable could start with a minus and it would still work. 

My wife often saids to me that I over think things and don't see the obvious. This is a classic example.

Thanks for your help