Embedded [FMP-Inlineaction] tages

[Kittrick]

I have two databases. Let's call them database A and database B. Both have the first and last names of the same person. Database A holds all of the employee stuff like their addresses and bio stuff. B logs all of the times they clock in and out of the system. I have a list of all of the employees names from Database A using an [fmp-inlineaction] tag. But now I would like to have all the hours they worked during the pay period in Databse B appear next to their name in the list compiled using Database A. I know this works because I tried it, but it only works if I use the [fmp-inlineaction] tag to search for only one name on the list. It looks like this:

[FMP-InlineAction:-db=a.fp5,-op=neq,archive=1,-sortfield=first_name,-sortorder=ascending,-Find]

[fmp-record]

[fmp-field:first_name], [fmp-field:last_name]  [fmp-field:title] [fmp-field:pay_rate] 

[FMP-InlineAction:-db=b.fp5,-op=gte,todays_date=9/16/2002,-op=lte,todays_date=9/30/2002,-find]

[fmp-field:grandtotaltime_rounded]<br>

[/fmp-inlineaction]

<hr length=5000>

[/fmp-record]

[/fmp-inlineaction]

Any suggestions???

[Anatoli]

I do not know exactly what is the problem.

Couldn't you just use related calculation in A to display the info from B without searching the B?

[Kittrick]

Hi Anatoli,

There is Database A and Database B. Database A has only bio and employee specific things like address, social security number, pay rate and emergency contact. Database B only handles their time in and out for an automatically generated tmecard. Each employee has their name in both databases, but when I run a nested [fmp-inlineaction] search to pull a running total of each time each employee clocked in and out of the system, I can't get any info except one total time worked for ALL EMPLOYEES. In other words, on my report it is not showing any specific total times for any individual employee, but a total time calculated for all employees together.

Here's an example. The following code:

<!-------Start Code----->

[FMP-InlineAction:-db=DatabaseA.fp5,-op=neq,archive=1,-Find]

[fmp-record]

[fmp-field:last_name], [fmp-field:first_name]&nbsp;&nbsp;[fmp-field:title]&nbsp;[fmp-field:pay_rate]&nbsp;

[/fmp-inlineaction]

<hr length=5000>

[/fmp-record]

<!-------End Code---->

Would yield the following result from DatabaseA:

<!------Start Example---->

Schmoe, Joe Foreman 6.00

--------------------------------------------

Frost, Jack Trainee 8.00

---------------------------------------------

Gilmour, Happy Golf Pro 10.00

----------------------------------------------

<!--------End Example----->

Now the hours they logged in and out of the system are stuck in DatabaseB. I can get the total hours each employee worked during the pay period on their individual timecards, but not if I try to stick it on the report in the example above using nested [fmp-inlineaction] tags.

Furthermore, I've gotten the correct total hours worked if I put the second nested [fmp-inlineaction] tag to search only for a specific employee in the list above. Here's an example:

<!-------Start Code---->

[FMP-InlineAction:-db=DatabaseA.fp5,-op=neq,archive=1,-Find]

[fmp-record]

[fmp-field:last_name], [fmp-field:first_name]&nbsp;&nbsp;[fmp-field:title]&nbsp;[fmp-field:pay_rate]&nbsp;

[FMP-InlineAction:-db=DatabaseB.fp5,last_name=Gilmour,first_name=Happy,-op=gte,todays_date=9/16/2002,-op=lte,todays_date=9/30/2002,-find]

[fmp-field:grandtotaltime_rounded]<br>

[/fmp-inlineaction]

<hr length=5000>

[/fmp-record]

<!----End Code---->

Would result in:

<!------Start Example---->

Schmoe, Joe Foreman 6.00 12

--------------------------------------------

Frost, Jack Trainee 8.00 12

---------------------------------------------

Gilmour, Happy Golf Pro 10.00 12

----------------------------------------------

<!--------End Example----->

Happy Gilmour worked 12 hours which shows. So it works only if I search for a specific employee. But what I need on this report is every employee to have a total time next to their hours.

Any suggestions??

[Garry Claridge]

You may need the "-lay" tag in your [FMP-InlineAction].

Garry

[Garry Claridge]

The other way to do this is by using some Javascript. An example exists on the Forum; I will have a look for it.

All the best.

Garry

[Garry Claridge]

I have re-read your question! I would use a Portal in databaseA to relate to databaseB. You can then use a Calculated field in databaseA to 'Sum()' the "time" from databaseB.

The cdml can then display the Calculated field next to the other details.

Hope this helps.

Garry

[Kittrick]

Hey Gary,

Thanks for your reply. The times are already calculated as a running total in DatabaseB. So if I searched for a specific employee to get their times, I can do it with the nested [fmp-inlineaction] tags. But as a list to match up names in one database and get their respective times to go along with it in another database, I do imagine I need some sort of relationship thing. Thanks again.

[Garry Claridge]

I recommend creating a Relationship from databaseA to databaseB based on a unique identifier such as whole Name or employee_id. Once that has been established the "running total" field (in databaseB) can be placed on a layout (no Portal needed) in databaseA.

Once this is done that field can be used on the web-page without an [FMP-InlineAction].

All the best.

Garry

[Anatoli]

Thank you Garry. I was thinking in similar way about this and you explained that nicely

[Garry Claridge]

I thought you were.

Garry

[Kittrick]

Hey again Gary,

I tried it with a portal. Everything worked except the running total individualized by employee. I still can't get the running total to be correct unless I run a search specifically on a specific employee.

Using web links I can trick one of my databses with the same fields as another to "pick up" the information I'm passing through such as First and Last name without a relationship. I was trynig to do that with nested [fmp-inlineaction] tags to pick up the first and last names from one database's find list and spit out their total hours worked per each employee. Have you ever done this?? I'm stuck still...any insight would be great:)

[Garry Claridge]

You may not need a Portal. Just place the "grandtotaltime_rounded" field on the Layout you use for the Web in databaseA. It will show the related value from databaseB. (The value it displays is the equivalent of the first Portal row; as if you had one.) You can display it next to the Names.

If you use a Portal, you can create a Calculated field in databaseA which is Sum(databaseB::time_worked). Display this field on the Web Layout and you can use it next to the Names.

For example:

[fmp-record]

[fmp-field:last_name], [fmp-field:first_name]&nbsp;&nbsp;[fmp-field:title]&nbsp;[fmp-field:pay_rate]&nbsp;[fmp-field:databaseB::grandtotaltime_rounded]<br>

<hr length=5000>

[/fmp-record]

Hope this helps.

Garry

[Kittrick]

Hi again Garry,

Thank you for your input. It helped a lot, but I still never really solved the problem. If I'm in DatabaseB, I can easily search one employee at a time and pull up all of the hours that they logged in as individuals by using a running total of hours between certain dates (the pay period) and by searching their first and last names.

Database A doesn't know about anything except Employee Name, and biographical info.

The field in or out of the portal still only shows a total of hours worked by all employees regardless of their name. I've tried using a weblink through databaseA and in the webpage had the following inline action to try and control the range of data :

<!----start example---->

[fmp-record]

[fmp-field:first_name] [fmp-field:last_name]

[fmp-inlineaction:-db=DatabaseB,-op=gte,todays_date=1/1/2002,-op=lte,todays_date=2/2/2002,find]

[fmp-field:time_worked]

<br>

[/fmp-inlineaction]

[/fmp-record]

<!----end example---->

Having the [fmp-inlineaction] control seems the most logical thing to have if it can "pick up" the last_name and first_name of the employees. Do I need to put last_name and first_name as part of the inline action?? What am I doing wrong??

[Garry Claridge]

You can't really use InlineActions like that. No direct way exists to pass information from the current record, of one database, to the InlineAction request.

The best way will be to use a Portal. The only remaining problem would be the date selections. You can overcome this with a little bit of Javascript.

[fmp-record]

[fmp-field:first_name] [fmp-field:last_name]

<script>nTotalHours = 0;

[fmp-portal:databaseB]

[fmp-if:(databaseB::todays_date.gte.01/01/2002).and.(databaseB::todays_date.lte.03/30/2002)]

nTotalHours += [fmp-field:databaseB::time_worked] ;

[/fmp-if]

[/fmp-portal]

document.write("Total Hours Worked = " + nTotalHours + "<br>");

</script>

[/fmp-record]

Hope this helps.

Garry

[Kittrick]

That's too bad that you can'y use inlne actions like that. It would be the perfect tag if you could. Oh well. Thanks for all your help and patience!