Crew Managment System

[Dayne_Brizan]

I’m new here

I’m creating an app to

1. monitor embarkation and disembarkation of crew by vessel

2. Dynamically show the crew onboard at the present date

3. Log crew changes as events sorted by vessel/date

4)Display a Count total crew onboard and disagregated by vessel

1. I would like to use enumlists that reference my Crew Table, to select the crew who embark (Crew-on) and disembark (Crew-off)
   The enumlist column for crew who disembark should be filtered to show only crew who were onboard that vessel before (not just a list of all Crew ).
   And the EnumList column for onsigners should filter out crew that are currently onboard any vessel since one person can only be on one vessel at a time. What expressions do I need to dynamic filter these list.

Example

I add 10 crew to my app

5 of them embark the Titanic on 4th July - so I select only Matthew ,Mark, Luke, John, Peter from total list of crew

On 18th August the Titanic comes to port and so my list of crew who can embark excludes the brothers above because they’re already and my possible list of those who are disembarking would consist of the 5 guys who were onboard.

2. and 4) Should I use a virtual Column in Vessels Table to show and count the Crew onboard ? How should that be expressed ?

3)The Log of crew changes is the only thing I’ve got to work but the contents are garbage

Crew are not permanently assigned to any vessel .

Appreciate any guidance

https://www.appsheet.com/newshortcut/5f6fa8ff-74e0-41c4-b78d-5df6257cef7c

[Aurelien]

Hi @Dayne_Brizan

I think you want something that looks (a bit) like this:
https://www.appsheet.com/samples/Employee-timesheet-log-with-historical-calendar?appGuidString=37224...
I encourage you to have a look under the hood un order to get some ideas.

Basically, what you need is pretty similar to a basic restaurant app, for taking orders and details.
Here is a post where you will find an app that can be useful for you:

Hi there, does anyone have a restaurant order... Questions

Hi @Komodowarno I have a basic demo app Food Orders 2 Which may give you some ideas. https://www.appsheet.com/portfolio/129805

Based on these, here is below my suggestions for your request.
Please keep in mind this is just a beginning, but that may help you:
Create a slice "Crew_ReadOnly"
To do so : go into Data/Slices/New Slice
Slice name “Crew_ReadOnly”
Source table “Crew”
Update mode “Read-Only”

Create a table "ChangeLog"
With columns:

• key, type text, initial value UNIQUEID()
• TimeStamp, type DateTime, initial value NOW()
• Vessel, type Ref, source table Vessel
• crew_getInVessel, type Yes_No
• any additional column you would want (user email…)

Create a table "CrewLog"
With columns:

• key
• changeLog, type Ref, source table ChangeLog, option Is a part of? ON
• crew_getInVessel, Editable : NO, Initial value expression [changeLog].[crew_getInVessel]
• vessel, type Ref, source table Vessel, Editable : NO, Initial value expression [changeLog].[Vessel]
• TimeStamp, type DateTime, Editable : NO, initial value [changeLog].[TimeStamp]
• crew, type Ref, Source table Crew_readOnly

Back to your Vessel Table
I suggest you create a virtual column and use FILTER(), SELECT(), COUNT() expression to count and extract informations you need.
For example, in order to get the crew for the last ChangeLog of a vessel, you will need:

• Column “last_changeLog”

LOOKUP(
  MAX(
    SELECT(
      ChangeLog[_ROWNUMBER],
      [_THISROW].[key] = [vessel]
    )
  ),
  "ChangeLog",
  "_ROWNUMBER",
  "key_changeLog"
)

then, the crew associated to this last “ChangeLog”

• “last_crew”
  [last_changeLog].[related CrewLogs]
  If you want to test if it was a onboarding or a landing
  [last_changeLog].[crew_getInVessel]

For reference, a very useful article:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Core concepts & functions Expressions: The Essentials FILTER() List Expressions and Aggregates LOOKUP() MAXROW() MINROW() REF_ROWS() SELECT() What is a Key? Adding & updating rows Is this a new row?/Does this row already exist? What is the previous value of this column? Excel alternatives How do I do COUNTIF() or COUNTIFS()? How do I do SUMIF() or SUMIFS()? How do I do VLOOKUP()? Last row of the spreadsheet How do I get the last row of the spreadsheet? How do I get a column value from…

for reference, useful documentation as well:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

Yes/No Expressions

Write expressions where the result is either TRUE or FALSE.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

[Dayne_Brizan]

Is it possible to select multiple items from a dropdown in a form ?

I’m able to create tabs in the forms for Crew joining and Leaving the vessel.I can also select crew from referenced tables/slices.

But how do I select more than one at a time ?

[Aurelien]

Hi @Dayne_Brizan

yes you can.
In the editor, you have to set the column type as EnumList.
Then, click on the black pen of this column, and pick base type “Ref”, and Reference table Name : “Crew”.

[Dayne_Brizan]

@Aurelien thank you for your thorough response . I will attempt to implement this method and continue to learn about the concepts.

I haven’t touched slices or expressions yet but you have provided good resources to improve my understanding.

Cheers .