Not sure what you mean by "Thru a dropdown/script trigger, the portal will show 1 "Blue", "Green", "Red", all under 1 project"...but I'm not sure it matters either.
Filter your relationship by project color AND by project...that way you are only associated with the related child records you want.
filtering your relationship will avoid getting crosstalk between your colors.
By the look of your calculation, you have four TOs involved: Sched; ProjX; SchedD; Phase. That's a fairly complex network. Check to make sure the relationships are all properly set up. Also, you mention using a portal; you will have to make sure the record on each row is able to independently relate to its correct context—it sounds like this may not be happening.
Good afternoon tomperr,
I hope your day is going well. I'm not sure I fully understand your example, so this idea may not work, but I would try using ExecuteSQL to populate a variable and than use GetAsDate (variable) to set the appropriate field. For example:
~fkID = Your foreign key field
~Green = ExecuteSQL (
"SELECT \"date information field\"
WHERE \"Identity\"=? AND \"Your foreign key field name\" = ?";
""; ""; “Blue"; ~fkID)
Hopefully this is helpful. If it doesn't accomplish your goal, you may want to post a more detailed example or sample file. Good luck. Have a great day!
Thanks for the feedback. Maybe there is an easier way to ask the question.
How can I compose a calculation for records in a portal based on another record within the same portal.
3 records with 3 fields. (colors, date, calculation).
Color Date calc
The calc field would say, if color is red, then "", if color is white return red's date of 1/1/16, if color is blue return white's date of 2/1/16,
I must confess i don't follow your logic, but that doesn't matter. What I suspect, though, is that you are confusing yourself by thinking through a portal. To simplify the issue, disregard the fact that you are looking at a portal. All the portal rows are separate records in the same table, so look at the issue from the point of view of that table. This means that what you want to do is calculate the value of a field based upon the value in another field—easy, except that you want FM to refer to that other field IN A DIFFERENT RECORD. That can also be done, but you have to provide FM with a way to decide which other record to refer to. To do that, you need to set up a self job relationship—where the TOs on each side of the relationship are from the same table: you want this record in this table to relate to this other record in this same table.
Good evening tomperr,
I'd like to try to restate your goal, as I understand it. It appears to me after re-reading your initial post and the clarification you just provided, that the colors reflect some sort of project status (e.g., blue = the project start date). Is that correct? If that is correct, would it also be accurate to say that the the other colors - red and green - use that project start date in a calculation to determine another project related date (e.g. green = (project start date + or - some amount))? If the answer to my second question is also yes, then I still believe that a possible solution may lie in the ExecuteSQL() function. The difference would be in the reference you use to select the appropriate project date. For example:
~Start = ExecuteSQL (
WHERE \"_pkID\" = ?";
""; ""; PortalTableName::_fkProjXID);
~ProjStartDate = GetAsDate( ~Start)
Sched::Identity = "Blue"; ~ProjStartDate;
Sched::Identity = "Red"; Your calculation;
Sched::Identity = "Green"; ~Your calculation;
Your calculation when there is no match
This is just a sample calculation, which you will have to tweak to make work for your specific application. Of course, I may have totally misrepresented your goal, in which case I apologize for wasting your time. Good luck And have a great night!
Good morning tomperr,
I woke up this morning an realized that in the calculation example I provided I failed to account for the format in which the ExecuteSQL() function will return the date information, which I believe is Unicode/SQL (I.e. YYYY-MM-DD). If you choose to implement ExecuteSQL() to retrieve date information via a Let() function, as I have described, you will need to replace the second variable declaration with something like the following:
~ProjStartDate = Date (
Middle ( ~Start; 5; 2);
Right (~Start; 2);
Left ( ~Start; 4)
Hopefully this is helpful. Have a great day!
I agree with Eric. Since you are using relationships in a portal, you simply need to add a global field to filter the portal based on a matching projectID. Your calculation should then work as is.
Hope this helps.
Your assumptions are correct. I fear the problem is in the relationship.
The portal records are based on the Schedule table. The phases table provides the phase (color) dependencies. The Sched_depend tables is the schedule table with dates to dependency which are used in calculation. This is where the problem exists. The calculation works if there is only 1 project schedule.
The SQL function is completely foreign to me.
See note I just posted.How would you suggest revising the relationship?
I think we need more perspective on the whole situation.
Do all projects go through the same color stages?
What is the order of color stages? Does it ever change order?
Is there a reason you use color instead of "Stage 1", "Stage 2" (this isn't really important...just curious)
How many color stages are there?
From what I'm seeing, it might be best to simply keep it all in one table and jettison the portal altogether.
Four stages, always the same colors in the same order...put stages 1-4 in your project table and have done with it...your math/logic will be a lot easier.
if (Sched::Identity = "Blue" ; ProjX::StartDate (this line works flawlessly)
; SchedD::EndDate + Phase::Days)
Do you have more relationships? (i.e. something called "Sched" and "SchedD") Can you show these on your screenshot as well?
For this exercise, all projects will go through the same color stages. Dates change per project.
After this is working, I will provide an option to insert custom colors (stages) without dependencies.
Stage 1, 2,.. etc can be substituted for colors. I was trying to simplify question using colors.
There are 45 color stages.
Can you provide an example of how "jettison the portal altogether" would work?
The stage order may change based on an alternate input field.
Thanks for input.